import os
from datetime import datetime
import json
import re
from io import BytesIO
from flask_executor import Executor
from docx import Document
from docx.enum.table import WD_ALIGN_VERTICAL
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.oxml import OxmlElement
from docx.oxml.ns import qn
from docx.shared import Inches, Pt
from docx.enum.table import WD_CELL_VERTICAL_ALIGNMENT

from flask_login import UserMixin
from sqlalchemy import inspect, MetaData, Table, text, and_
import openpyxl
import pandas as pd
from flask import send_file
from openpyxl.reader.excel import load_workbook
from openpyxl.styles import Border, Side, Font, Alignment
from sqlalchemy import func
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import joinedload
from zhipuai import ZhipuAI

from exts import db, create_uml_picture, backup_database
from toolbox import (check_data_flow, merge_string, is_connected_to_internet, format_data_flow, \
                     find_table_by_req_type, set_thin_solid_border, set_table_col_widths, get_scaled_width)
from collections import defaultdict

ai_model = "glm-4-flash"
zhipu_key = "35bde51915ad3771a2c0e8990da58c3f.O4N5AWOjSv6mGeqe"
client = ZhipuAI(api_key=zhipu_key)  #
zhipu=1
internet_status = False

user_types = {
    1: '管理员',
    2: '操作员'
}

# 用户管理表
class UserTable(db.Model, UserMixin):
    uid = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String, unique=True, nullable=False)
    pwd = db.Column(db.String, nullable=False)   # Password
    role = db.Column(db.String, nullable=False)  # user_types
    customer_id = db.Column(db.Integer, db.ForeignKey('customer_table.id', ondelete='SET DEFAULT'), default=0)  # 外键
    project_id = db.Column(db.Integer, db.ForeignKey('project_table.id', ondelete='SET DEFAULT'), default=0)  # 外键
    state = db.Column(db.Integer, nullable=False)   # 账号状态 1: 启用 ; 2: 停用
    phone = db.Column(db.String)
    email = db.Column(db.String)
    remark = db.Column(db.String)
    create_time = db.Column(db.String)
    update_time = db.Column(db.String)
    last_login = db.Column(db.String) # 最后登录时间
    # 定义关系
    project = db.relationship('ProjectTable', backref='users')
    customer = db.relationship('CustomerTable', backref='users')

    # 实现 UserMixin 的 get_id 方法
    def get_id(self):
        return str(self.uid)

    # 实现 is_active 属性
    @property
    def is_active(self):
        return self.state == 1  # 账号状态为 1 时表示激活

# 用户权限管理表
class PermissionTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer)
    project_id = db.Column(db.Integer)
    permission_type = db.Column(db.String(4), nullable=False)
    create_time = db.Column(db.String)
    update_time = db.Column(db.String)

# 日志
class LogTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer)
    create_time = db.Column(db.String)
    type = db.Column(db.String)
    log_info = db.Column(db.String)
    level = db.Column(db.Integer)   # 严重级别 :  1,2,3

# 客户表
class CustomerTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String, unique=True, nullable=False)
    name_en = db.Column(db.String)  #英文名
    remark = db.Column(db.String)
    create_time = db.Column(db.String)
    update_time = db.Column(db.String)

# 项目表
class ProjectTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String, unique=True, nullable=False)
    abbr = db.Column(db.String)  # 车型缩写  RE | RT | ...
    prefix = db.Column(db.String)  # 编号前缀  'CSI'
    desc = db.Column(db.String)   # 项目描述
    customer_id = db.Column(db.Integer, db.ForeignKey('customer_table.id', ondelete='SET DEFAULT'), default=0)  # 外键
    state = db.Column(db.String)   # 项目状态  待启动 | 进行中 | 已发布
    pm = db.Column(db.String)      # PM: name + phone
    create_time = db.Column(db.String)
    update_time = db.Column(db.String)
    # 定义关系
    customer = db.relationship('CustomerTable', backref='projects')


# ======================================
#     1 基础数据表 （全局）
# ======================================
# 1.1 安全属性表 （全局）
class SecurityAttrTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String)   # 可用性、完整性、机密性、...
    desc = db.Column(db.String)   #

# 1.2 ECU 特性列表
class EcuFeatureTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    feature = db.Column(db.String, nullable=False)   # 蓝牙，WiFi，...
    type = db.Column(db.String, nullable=False)   # 通讯接口/重要功能/..
    remark = db.Column(db.String)   # 备注

# 获取所有 feature
def get_all_features():
    return [row.feature for row in db.session.query(EcuFeatureTable.feature).distinct()]
    # return [row.feature for row in db.session.query(EcuFeatureTable.feature).distinct()]


# 1.3 特性 x 安全属性
class FeatureMapSecurityAttrTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    feature_id = db.Column(db.Integer, db.ForeignKey('ecu_feature_table.id', ondelete='CASCADE'))  # 外键
    security_attr_id = db.Column(db.Integer, db.ForeignKey('security_attr_table.id', ondelete='CASCADE'))   #
    req_name = db.Column(db.String)  # IR-Name
    # 定义关系
    feature = db.relationship('EcuFeatureTable', backref='feature_map_sas')
    security_attr = db.relationship('SecurityAttrTable', backref='feature_map_sas')

# 1.4 威胁类型
class ThreadTypeTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String)   # 拒绝服务、篡改、信息泄露、冒充、抵赖、权限提升、泄露
    security_attr = db.Column(db.String)   #
    desc = db.Column(db.String)   #

# 1.5 威胁评估参数表
class ThreadAssessmentParaTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String)   #
    value = db.Column(db.Integer)   #
    type = db.Column(db.String)   # 发现漏洞所需要的时间，攻击者专业水平，...
    standard = db.Column(db.String)   #

# 1.6 风险评估参数表
class RiskAssessmentParaTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String)   # 拒绝服务、篡改、信息泄露、冒充、抵赖、权限提升、泄露
    explain = db.Column(db.String)   #
    value = db.Column(db.Integer)
    type = db.Column(db.String)   #
    standard = db.Column(db.String)   #
# ======================================
#     2 系统建模 （基于项目做隔离）
# ======================================
# 2.1 ECU列表
class EcuTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String, nullable=False)   #
    abbr = db.Column(db.String, nullable=False)   # 缩写
    abbr_ext = db.Column(db.String) # 缩写, 带供应商信息

    # 硬件
    pcb = db.Column(db.String)
    chipset = db.Column(db.String)

    # 系统软件
    Bootloader = db.Column(db.String)

    # OS
    OS = db.Column(db.String)  # 通用操作系统
    Linux = db.Column(db.String)
    Windows = db.Column(db.String)
    Android = db.Column(db.String)
    QNX = db.Column(db.String)
    HarmonyOS = db.Column(db.String)
    RTOS = db.Column(db.String)

    # 应用软件
    app = db.Column(db.String)      # OEM原生应用
    app_3rd = db.Column(db.String)  # 第三方应用

    # 数据
    data_1 = db.Column(db.String)  # 重要数据
    data_2 = db.Column(db.String)  # 个人数据
    data_3 = db.Column(db.String)  # 关键数据

    # added 2024-12-30
    data_key = db.Column(db.String) # 密钥
    data_car_info = db.Column(db.String) # 车辆识别数据
    data_log = db.Column(db.String) # 日志
    data_export = db.Column(db.String) # 数据外发
    data_cross_border = db.Column(db.String) # 数据跨境

    # 物理接口
    interface_usb = db.Column(db.String)  # USB接口
    interface_sd = db.Column(db.String)  # SD卡接口
    interface_debug = db.Column(db.String)  # 调试接口
    # interface_1 = db.Column(db.String)  #

    # 内部通信
    can_bus = db.Column(db.String)  # CAN通信
    eth_bus = db.Column(db.String)  # 以太通信
    flex_ray_bus = db.Column(db.String)  # FlexRay通信
    lin_bus = db.Column(db.String)  # LIN通信
    # _bus = db.Column(db.String)  #

    # 无线通信
    wireless_bt = db.Column(db.String)  # 蓝牙
    wireless_wifi = db.Column(db.String)  # WIFI
    wireless_rf = db.Column(db.String)  # 射频通信
    wireless_v2x = db.Column(db.String)  # V2X

    # 远程通信
    wireless_gnss = db.Column(db.String)  # GNSS
    wireless_mobile = db.Column(db.String)  # 蜂窝通信

    # 重要功能
    ota_upgrade = db.Column(db.String)  # OTA升级
    usb_upgrade = db.Column(db.String)  # USB升级
    obd_upgrade = db.Column(db.String)  # OBD升级
    obd_debug = db.Column(db.String)  # OBD诊断
    remote_debug = db.Column(db.String) #远程诊断

    other_remote_cmd = db.Column(db.String)  #其他远程控制指令


    intrusion_detection_prevention_systems = db.Column(db.String)  # IDPS - Intrusion Detection and Prevention Systems
    upgrade_node = db.Column(db.String)  # 车载升级主控节点
    tbox = db.Column(db.String)  # TBox
    gate_way = db.Column(db.String)  # 网关

    # CAL 等级   影响级别	攻击向量	CAL等级
    affect_level = db.Column(db.String)
    attack_type = db.Column(db.String)
    cal_level = db.Column(db.String)

    # 供应商管理
    RP = db.Column(db.String)  # Responsible Person
    RP_department = db.Column(db.String)  # 负责部门

    phone1 = db.Column(db.String)
    email1 = db.Column(db.String)

    safety_contact_person = db.Column(db.String)  # 安全联系人
    phone2 = db.Column(db.String)
    email2 = db.Column(db.String)

    vendor = db.Column(db.String)
    vendor_person = db.Column(db.String)
    vendor_phone = db.Column(db.String)
    vendor_email = db.Column(db.String)

    remark = db.Column(db.String)   # 备注, 需求数量
    Project_id = db.Column(db.Integer, db.ForeignKey('project_table.id', ondelete='CASCADE'))  # 外键
    # 定义关系
    project = db.relationship('ProjectTable', backref='ecus')

# 2.2 ECU 与特性关联表 (某个ECU 支持 哪些特性)
class EcuMapFeatureTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    ecu_id = db.Column(db.Integer, db.ForeignKey('ecu_table.id', ondelete='CASCADE'))  # 外键
    feature_id = db.Column(db.Integer, db.ForeignKey('ecu_feature_table.id', ondelete='CASCADE'))  # 外键
    os = db.Column(db.String)  # Android, Linux, Windows, HarmonyOS, RTOS
    project_id = db.Column(db.Integer, db.ForeignKey('project_table.id', ondelete='CASCADE'))  # 外键

    # 定义关系
    project = db.relationship('ProjectTable', backref='ecu_map_features')
    ecu = db.relationship('EcuTable', backref='ecu_map_features')
    feature = db.relationship('EcuFeatureTable', backref='ecu_map_features')

# 生成 CREATE VIEW 的 SQL 语句
def generate_view_sql():
    features = get_all_features()
    feature_columns = ', '.join(
        [f'MAX(CASE WHEN SUBSTR(ef.feature, 4) = "{feature}" THEN "√" ELSE " " END) AS "{feature.replace(" ", "_")}"' for feature in
         features])

    sql = f"""
        CREATE VIEW ecu_map_feature_view AS
        SELECT
            ecu.abbr AS ecu_abbr,
            {feature_columns}
        FROM
            ecu_table ecu
        LEFT JOIN
            ecu_map_feature_table emft ON ecu.id = emft.ecu_id
        LEFT JOIN
            ecu_feature_table ef ON emft.feature_id = ef.id
        GROUP BY
            ecu.abbr
        """
    return sql


# 2.3 功能相关性表
class FuncRelationTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    func_id= db.Column(db.String)
    name = db.Column(db.String, nullable=False)   # 功能名字，例如 前雾灯控制
    code = db.Column(db.Integer)  # 0000 4位数编号
    item = db.Column(db.String)
    func_domain_id = db.Column(db.Integer, db.ForeignKey('item_table.id', ondelete='CASCADE'))  # 外键 2.4
    system=db.Column(db.String)  # 子系统
    desc = db.Column(db.String)  # 功能说明
    data_flow = db.Column(db.String) # 开关>>仪表>>网关>>BCM>>灯光
    check=db.Column(db.String)       # 检查： 列出ECU字段
    contact_person = db.Column(db.String)
    bool_ee = db.Column(db.String)          # 是否为电子电气EE架构下的组件（内部通过各种总线通信）  Y/N
    bool_safe = db.Column(db.String)        # 是否会影响车辆功能安全相关操作
    bool_userdata = db.Column(db.String)    # 是否收集或处理用户或者敏感数据（参考附录1）
    bool_network = db.Column(db.String)     # 是否基于网络化的ECU实现车辆功能（1.支持远程4G/5G通信  2.支持近端无线通信， WiFi/蓝牙/NFC/胎压检测,支持V2X 3.支持物理接口访问，USB、OBD等 ）
    result = db.Column(db.String)           # 基于上述信息综合判断 : Y / N
    project_id = db.Column(db.Integer, db.ForeignKey('project_table.id', ondelete='CASCADE'))  # 外键
    # 定义关系
    project = db.relationship('ProjectTable', backref='func_relations')
    func_domain = db.relationship('ItemTable', backref='func_relations')

# 2.4 Item 表
class ItemTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String, nullable=False)  # 功能名字，例如 前雾灯控制
    number = db.Column(db.String)  #
    system = db.Column(db.String)  # 所属系统/子系统: 车身域、底盘域 ...
    desc = db.Column(db.String)    # 描述: 功能名 相加
    data_flow = db.Column(db.String)  # 各功能的数据流 叠加
    ecu_list = db.Column(db.String)   # 有哪些相关的ECU
    plant_uml = db.Column(db.String)
    # add S F O P
    safety_impact_level = db.Column(db.String)
    safety_impact_desc = db.Column(db.String)
    economic_impact_level = db.Column(db.String)
    economic_impact_desc = db.Column(db.String)
    operation_impact_level = db.Column(db.String)
    operation_impact_desc = db.Column(db.String)
    privacy_impact_level = db.Column(db.String)
    privacy_impact_desc = db.Column(db.String)

    del_flag = db.Column(db.Integer, default=0)  # 1 - 待删除,  0 - 不删除
    project_id = db.Column(db.Integer, db.ForeignKey('project_table.id', ondelete='CASCADE'))  # 外键
    # 定义关系
    project = db.relationship('ProjectTable', backref='items')

# ======================================
#     3 威胁分析 （基于项目做隔离）
# ======================================
# 3.1 威胁分析表
class ThreatAnalysisTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    ecu_id = db.Column(db.Integer, db.ForeignKey('ecu_table.id', ondelete='CASCADE'))              #
    ecu_feature_id = db.Column(db.Integer, db.ForeignKey('ecu_feature_table.id', ondelete='CASCADE'))      # ->1.2
    security_attr_id = db.Column(db.Integer, db.ForeignKey('security_attr_table.id', ondelete='CASCADE'))    # ->1.1
    thread_type_id = db.Column(db.Integer, db.ForeignKey('thread_type_table.id', ondelete='CASCADE'))      # ->1.4
    attack_path_number = db.Column(db.String)       # 攻击路径编号
    attack_path = db.Column(db.String)              # 攻击路径
    thread_scenario_number = db.Column(db.String)   # 威胁场景编号
    thread_scenario = db.Column(db.String)          # 威胁场景
    vdt_id = db.Column(db.Integer, db.ForeignKey('thread_assessment_para_table.id',  ondelete='SET DEFAULT'), default=0)              # Vulnerability Discovery Time  发现漏洞时间
    asl_id = db.Column(db.Integer,  db.ForeignKey('thread_assessment_para_table.id',  ondelete='SET DEFAULT'), default=0)              # Attacker Skill Level
    knowledge_level_id = db.Column(db.Integer, db.ForeignKey('thread_assessment_para_table.id',  ondelete='SET DEFAULT'), default=0)  # Knowledge Level
    woo_id = db.Column(db.Integer,  db.ForeignKey('thread_assessment_para_table.id',  ondelete='SET DEFAULT'), default=0)              # 机会窗口 Window of Opportunity
    device_id = db.Column(db.Integer, db.ForeignKey('thread_assessment_para_table.id',  ondelete='SET DEFAULT'), default=0)       # 设备 id
    attack_value = db.Column(db.Integer)    # 攻击可行性值
    attack_level = db.Column(db.String)     # 攻击可行性等级
    # cybersecurity_req_id = db.Column(db.Integer, db.ForeignKey('cybersecurity_req_table.id'))  # -> 4.4 网络安全需求
    project_id = db.Column(db.Integer, db.ForeignKey('project_table.id', ondelete='CASCADE'))  # 基于项目做隔离
    # 定义关系
    project = db.relationship('ProjectTable', backref='threat_analysis_tables')
    ecu = db.relationship('EcuTable', backref='threat_analysis_tables')
    ecu_feature = db.relationship('EcuFeatureTable', backref='threat_analysis_tables')
    security_attr = db.relationship('SecurityAttrTable', backref='threat_analysis_tables')
    thread_type = db.relationship('ThreadTypeTable', backref='threat_analysis_tables')
    vdt = db.relationship('ThreadAssessmentParaTable', foreign_keys=[vdt_id])
    asl = db.relationship("ThreadAssessmentParaTable", foreign_keys=[asl_id])
    knowledge_level = db.relationship("ThreadAssessmentParaTable", foreign_keys=[knowledge_level_id])
    woo = db.relationship("ThreadAssessmentParaTable", foreign_keys=[woo_id])
    device = db.relationship("ThreadAssessmentParaTable", foreign_keys=[device_id])
    # cybersecurity_req = db.relationship('CybersecurityReqTable', backref='threat_analysis_tables')

# 共享经验库
class ThreatAnalysisShareTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    ecu_name = db.Column(db.String)
    ecu_abbr = db.Column(db.String)
    feature_name = db.Column(db.String)
    security_attr_name = db.Column(db.String)
    thread_type_name = db.Column(db.String)
    thread_type_sa = db.Column(db.String) # 安全属性 : 可用，完整，机密...
    attack_path_number = db.Column(db.String)       # 攻击路径编号
    attack_path = db.Column(db.String)              # 攻击路径
    thread_scenario_number = db.Column(db.String)   # 威胁场景编号
    thread_scenario = db.Column(db.String)          # 威胁场景
    vdt_id = db.Column(db.Integer, db.ForeignKey('thread_assessment_para_table.id',  ondelete='SET DEFAULT'), default=0)              # Vulnerability Discovery Time  发现漏洞时间
    asl_id = db.Column(db.Integer,  db.ForeignKey('thread_assessment_para_table.id',  ondelete='SET DEFAULT'), default=0)              # Attacker Skill Level
    knowledge_level_id = db.Column(db.Integer, db.ForeignKey('thread_assessment_para_table.id',  ondelete='SET DEFAULT'), default=0)  # Knowledge Level
    woo_id = db.Column(db.Integer,  db.ForeignKey('thread_assessment_para_table.id',  ondelete='SET DEFAULT'), default=0)              # 机会窗口 Window of Opportunity
    device_id = db.Column(db.Integer, db.ForeignKey('thread_assessment_para_table.id',  ondelete='SET DEFAULT'), default=0)       # 设备 id
    attack_value = db.Column(db.Integer)    # 攻击可行性值
    attack_level = db.Column(db.String)     # 攻击可行性等级

    # 定义关系
    vdt = db.relationship('ThreadAssessmentParaTable', foreign_keys=[vdt_id])
    asl = db.relationship("ThreadAssessmentParaTable", foreign_keys=[asl_id])
    knowledge_level = db.relationship("ThreadAssessmentParaTable", foreign_keys=[knowledge_level_id])
    woo = db.relationship("ThreadAssessmentParaTable", foreign_keys=[woo_id])
    device = db.relationship("ThreadAssessmentParaTable", foreign_keys=[device_id])

# ======================================
#     4 风险评估（基于项目做隔离）
# ======================================
# 4.1 ECU级风险评估 ECU-level Risk Assessment
class RiskAssessmentEcuTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    item_id = db.Column(db.Integer, db.ForeignKey('item_table.id', ondelete='CASCADE'))  # 功能 id
    ecu_id = db.Column(db.Integer, db.ForeignKey('ecu_table.id', ondelete='CASCADE'))  #
    ecu_feature_id = db.Column(db.Integer, db.ForeignKey('ecu_feature_table.id', ondelete='CASCADE'))  #EcuMapFeatureTable
    asset_code = db.Column(db.String)   # 资产编码，自动生成 CSI-RE001-ESP-DSP
    feature_map_security_attr_id = db.Column(db.Integer, db.ForeignKey('security_attr_table.id', ondelete='CASCADE'))  #
    threat_analysis_id = db.Column(db.Integer, db.ForeignKey('threat_analysis_table.id', ondelete='CASCADE'))  # 3.1
    damage_scenario_desc = db.Column(db.String) #损害场景描述
    risk_code = db.Column(db.String)            # 风险编号 RISK-RE001-ESP-DSP-01
    safe_affect_id = db.Column(db.Integer, db.ForeignKey('risk_assessment_para_table.id', ondelete='CASCADE'))      # 安全影响id  RiskAssessmentParaTable.id
    economy_affect_id = db.Column(db.Integer, db.ForeignKey('risk_assessment_para_table.id', ondelete='CASCADE'))   # 经济影响id
    operator_affect_id = db.Column(db.Integer, db.ForeignKey('risk_assessment_para_table.id', ondelete='CASCADE'))  # 操作影响id
    private_affect_id = db.Column(db.Integer, db.ForeignKey('risk_assessment_para_table.id', ondelete='CASCADE'))   # 隐私影响id
    affect_value = db.Column(db.Integer)    # 影响值 = Sum(RiskAssessmentParaTable.value)
    affect_level = db.Column(db.String)     # 影响等级 = IF(V255="N/A","N/A",IF(V255>=1000,"严重的",IF(V255>=100,"重大的",IF(V255>=10,"中等的","无明显影响的"))))
    risk_level = db.Column(db.String)       # 风险等级 = 基于threat_analysis_id 和 影响等级 查表
    risk_treatment_strategy_id = db.Column(db.Integer, db.ForeignKey('risk_treatment_strategy_table.id')) # 风险处置策略: '风险接受'、风险转移、风险回避、风险降低

    # 安全目标 （多个目标对应同一个安全需求）当选择'风险降低'时有效
    cybersecurity_goal_name = db.Column(db.String)  # 安全目标名字 应保护<资产编码>的<安全属性>
    cybersecurity_goal_code = db.Column(db.String)  # 编号 'CG-RE-0001' 导出的时候自动生成
    #
    cybersecurity_req_id = db.Column(db.Integer, db.ForeignKey('cybersecurity_req_table.id'))  # -> 4.4 网络安全需求
    #
    r_attack_feasibility_level = db.Column(db.String, default='-')   # 残余攻击可行性等级 Residual Attack Feasibility Level       固定 '极低'
    r_risk_est_r_level = db.Column(db.String)           # 残余风险预估风险等级 Residual Risk Estimated Risk Level    按规则判断
    r_risk_treatment_strategy_id = db.Column(db.Integer, db.ForeignKey('risk_treatment_strategy_table.id'), default=4)  # 固定：'风险接受'


    record = db.Column(db.String)                   # 风险状况回顾记录
    project_id = db.Column(db.Integer, db.ForeignKey('project_table.id', ondelete='CASCADE'))

    # 定义关系
    project = db.relationship('ProjectTable', backref='risk_assessment_ecu_levels')
    item = db.relationship('ItemTable', backref='risk_assessment_ecu_levels')
    ecu = db.relationship('EcuTable', backref='risk_assessment_ecu_levels')
    ecu_feature = db.relationship('EcuFeatureTable', backref='risk_assessment_ecu_levels')
    security_attr = db.relationship('SecurityAttrTable', backref='risk_assessment_ecu_levels')
    threat_analysis = db.relationship('ThreatAnalysisTable', backref='risk_assessment_ecu_levels')
    safe_affect = db.relationship('RiskAssessmentParaTable', foreign_keys=[safe_affect_id])
    economy_affect = db.relationship('RiskAssessmentParaTable', foreign_keys=[economy_affect_id])
    operator_affect = db.relationship('RiskAssessmentParaTable', foreign_keys=[operator_affect_id])
    private_affect = db.relationship('RiskAssessmentParaTable', foreign_keys=[private_affect_id])
    cybersecurity_req = db.relationship('CybersecurityReqTable', backref='risk_assessment_ecu_levels')
    risk_treatment_strategy = db.relationship('RiskTreatmentStrategyTable', foreign_keys=[risk_treatment_strategy_id])
    r_risk_treatment_strategy = db.relationship('RiskTreatmentStrategyTable', foreign_keys=[r_risk_treatment_strategy_id])

# 共享经验库
class RiskAssessmentShareTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    item_name = db.Column(db.String)
    item_desc = db.Column(db.String)

    ecu_name = db.Column(db.String)
    ecu_abbr = db.Column(db.String)

    feature_name = db.Column(db.String)
    security_attr_name = db.Column(db.String)
    threat_analysis_id = db.Column(db.Integer, db.ForeignKey('threat_analysis_share_table.id', ondelete='CASCADE'))  # 3.1
    damage_scenario_desc = db.Column(db.String) #损害场景描述

    safe_affect_id = db.Column(db.Integer, db.ForeignKey('risk_assessment_para_table.id', ondelete='CASCADE'))      # 安全影响id  RiskAssessmentParaTable.id
    economy_affect_id = db.Column(db.Integer, db.ForeignKey('risk_assessment_para_table.id', ondelete='CASCADE'))   # 经济影响id
    operator_affect_id = db.Column(db.Integer, db.ForeignKey('risk_assessment_para_table.id', ondelete='CASCADE'))  # 操作影响id
    private_affect_id = db.Column(db.Integer, db.ForeignKey('risk_assessment_para_table.id', ondelete='CASCADE'))   # 隐私影响id
    affect_value = db.Column(db.Integer)    # 影响值 = Sum(RiskAssessmentParaTable.value)
    affect_level = db.Column(db.String)     # 影响等级 = IF(V255="N/A","N/A",IF(V255>=1000,"严重的",IF(V255>=100,"重大的",IF(V255>=10,"中等的","无明显影响的"))))
    risk_level = db.Column(db.String)       # 风险等级 = 基于threat_analysis_id 和 影响等级 查表
    risk_treatment_strategy_id = db.Column(db.Integer, db.ForeignKey('risk_treatment_strategy_table.id')) # 风险处置策略: '风险接受'、风险转移、风险回避、风险降低

    # 安全目标 （多个目标对应同一个安全需求）当选择'风险降低'时有效
    cybersecurity_goal_name = db.Column(db.String)  # 安全目标名字 应保护<资产编码>的<安全属性>
    #
    cybersecurity_req_name = db.Column(db.String)   # 需要特殊处理，跟原始表格不同
    #
    r_attack_feasibility_level = db.Column(db.String, default='-')   # 残余攻击可行性等级 Residual Attack Feasibility Level       固定 '极低'
    r_risk_est_r_level = db.Column(db.String)           # 残余风险预估风险等级 Residual Risk Estimated Risk Level    按规则判断
    r_risk_treatment_strategy = db.Column(db.String)

    # 定义关系
    safe_affect = db.relationship('RiskAssessmentParaTable', foreign_keys=[safe_affect_id])
    economy_affect = db.relationship('RiskAssessmentParaTable', foreign_keys=[economy_affect_id])
    operator_affect = db.relationship('RiskAssessmentParaTable', foreign_keys=[operator_affect_id])
    private_affect = db.relationship('RiskAssessmentParaTable', foreign_keys=[private_affect_id])

    threat_analysis = db.relationship('ThreatAnalysisShareTable', backref='risk_assessment_shares')
    risk_treatment_strategy = db.relationship('RiskTreatmentStrategyTable', foreign_keys=[risk_treatment_strategy_id])

# 4.2 风险处置策略: 风险接受、风险转移、风险回避、风险降低
class RiskTreatmentStrategyTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String)  #  风险接受、风险转移、风险回避、风险降低
    desc = db.Column(db.String)  #  风险处置描述  风险接受、风险转移: 网络安全声明     # 风险回避: 原因备注id

# 4.3 网络安全需求 cybersecurity_req_table
class CybersecurityReqTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    type = db.Column(db.String)     # 分类: 硬件/系统软件/...  手工输入
    feature = db.Column(db.String)  # 与特性表对应

    # IR/OR 原始需求
    name_or = db.Column(db.String)  # 不能重复
    code_or = db.Column(db.String)  # 编号   IR-001
    desc_or = db.Column(db.String)  # 描述

    # DR 设计需求
    name_dr = db.Column(db.String)  # DR名字, 不重复
    code_dr = db.Column(db.String)  # 编号   SR-001-01
    desc_dr = db.Column(db.String)  # 说明

    state = db.Column(db.String)  #  状态：
    # 网络安全保证等级
    ca_level = db.Column(db.String)  # CAL1,CAL2,CAL3,CAL4
    cal1 = db.Column(db.String)
    cal2 = db.Column(db.String)
    cal3 = db.Column(db.String)
    cal4 = db.Column(db.String)


    # 需求依据
    req_basis = db.Column(db.String) # R155 | GB 44495 | ...
    r155 = db.Column(db.String)
    gb44495 = db.Column(db.String)
    r156 = db.Column(db.String)
    gb44496 = db.Column(db.String)
    other = db.Column(db.String)

    #
    gb_new_req = db.Column(db.String)
    remark = db.Column(db.String)

    project_id = db.Column(db.Integer, db.ForeignKey('project_table.id', ondelete='CASCADE'))

    # 定义关系
    project = db.relationship('ProjectTable', backref='cybersecurity_reqs')


# 5.1 资产场景描述  AssetScenario Description#
class AssetScenarioDescTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    ecu_feature_id = db.Column(db.Integer, db.ForeignKey('ecu_feature_table.id', ondelete='CASCADE'))  # ->1.2
    security_attr_id = db.Column(db.Integer, db.ForeignKey('security_attr_table.id', ondelete='CASCADE'))  # ->1.1

    attack_path = db.Column(db.String)      # 攻击路径
    thread_scenario = db.Column(db.String)  # 威胁场景
    damage_scenario_desc = db.Column(db.String)  # 损害场景描述
    person = db.Column(db.String)
    remark = db.Column(db.String)

    ecu_feature = db.relationship('EcuFeatureTable', backref='asset_scenario_desc_tables')
    security_attr = db.relationship('SecurityAttrTable', backref='asset_scenario_desc_tables')

# 5.2 资产危害场景评估  AssetRiskScenarioAssessment
class AssetRiskScenarioAssessmentTable(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    ecu_feature_id = db.Column(db.Integer, db.ForeignKey('ecu_feature_table.id', ondelete='CASCADE'))  # ->1.2
    security_attr_id = db.Column(db.Integer, db.ForeignKey('security_attr_table.id', ondelete='CASCADE'))  # ->1.1
    type = db.Column(db.String)  # S/F/O/P
    level = db.Column(db.String)

    out_level = db.Column(db.String)  #

    ecu_feature = db.relationship('EcuFeatureTable', backref='asset_risk_tables')
    security_attr = db.relationship('SecurityAttrTable', backref='asset_risk_tables')


def copy_tara_table_to_share():
    records = ThreatAnalysisTable.query.filter_by(project_id=0).all()
    count = 0
    for record in records:
        if record.ecu is None or record.security_attr is None or record.thread_type is None:
            print(f'指针为空, delete')
            db.session.delete(record)
            continue

        result = ThreatAnalysisShareTable.query.filter_by(ecu_abbr=record.ecu.abbr,
                                                          feature_name=record.ecu_feature.feature,
                                                          security_attr_name=record.security_attr.name,
                                                          thread_type_name=record.thread_type.name).first()
        if result:
            print(f'找到重复项, skip ')
            continue

        cache = ThreatAnalysisShareTable(
            ecu_name=record.ecu.name,
            ecu_abbr=record.ecu.abbr,
            feature_name=record.ecu_feature.feature,
            security_attr_name=record.security_attr.name,
            thread_type_name=record.thread_type.name,
            thread_type_sa=record.thread_type.security_attr,
            attack_path_number='-',
            attack_path=record.attack_path,
            thread_scenario_number='',
            thread_scenario=record.thread_scenario,
            vdt_id=record.vdt_id,
            asl_id=record.asl_id,
            knowledge_level_id=record.knowledge_level_id,
            woo_id=record.woo_id,
            device_id=record.device_id,
            attack_value=record.attack_value,
            attack_level=record.attack_level
        )
        db.session.add(cache)
        count += 1
        # print(f'Copy: {record.ecu.abbr} - {record.ecu_feature.feature} - {record.security_attr.name}')
    db.session.commit()
    print(f'create {count} ThreatAnalysisShareTable finished.')

    records = RiskAssessmentEcuTable.query.filter_by(project_id=0).all()
    count = 0
    for record in records:
        if record.ecu is None or record.item is None:
            print(f'Copy RiskAssessment : {record.id} item/ecu is None, skip')
            db.session.delete(record)
            continue

        threat_analysis = (ThreatAnalysisShareTable
                           .query
                           .filter_by(ecu_abbr=record.ecu.abbr,
                                      feature_name=record.ecu_feature.feature,
                                      security_attr_name=record.security_attr.name).first())
        if threat_analysis is None:
            print(f'Error:: 找不到对应的 threat_analysis ({record.id},{record.ecu.abbr}, {record.ecu_feature.feature}, {record.security_attr.name})')
            continue

        cybersecurity_req_name = ''
        if record.cybersecurity_req_id:
            cybersecurity_req_name = record.cybersecurity_req.name_or

        result = RiskAssessmentShareTable.query.filter_by(item_name=record.item.name,
                                                          ecu_abbr=record.ecu.abbr,
                                                          feature_name=record.ecu_feature.feature,
                                                          security_attr_name=record.security_attr.name).first()
        if result:
            print(f'RiskAssessmentShareTable 重复, skip...')
            continue

        # print(f'Copy RiskAssessment: {record.id}')
        cache = RiskAssessmentShareTable(
            item_name=record.item.name,
            item_desc=record.item.desc,

            ecu_name=record.ecu.name,
            ecu_abbr=record.ecu.abbr,
            feature_name=record.ecu_feature.feature,
            security_attr_name=record.security_attr.name,
            threat_analysis_id=threat_analysis.id,
            damage_scenario_desc=record.damage_scenario_desc,

            safe_affect_id=record.safe_affect_id,
            economy_affect_id=record.economy_affect_id,
            operator_affect_id=record.operator_affect_id,
            private_affect_id=record.private_affect_id,

            affect_value=record.affect_value,
            affect_level=record.affect_level,
            risk_level=record.risk_level,
            risk_treatment_strategy_id=record.risk_treatment_strategy_id,
            cybersecurity_goal_name=record.cybersecurity_goal_name,
            cybersecurity_req_name=cybersecurity_req_name, # 安全需求
            r_attack_feasibility_level = '--', # 残余攻击可行性等级 Residual Attack Feasibility Level       固定 '极低'
            r_risk_est_r_level = '',
            r_risk_treatment_strategy=r'/'
        )
        db.session.add(cache)
        count += 1
        # print(f'Copy: {record.item.name} - {record.ecu.abbr} - {record.ecu_feature.feature} - {record.security_attr.name}')

    db.session.commit()
    print(f'create {count} RiskAssessmentShareTable')

ThreadScenes = {
    '冒充': [
        "攻击者通过克隆车辆的RFID标签来欺骗车辆门禁系统。",
        "攻击者使用伪造的蓝牙钥匙来解锁和启动车辆。",
        "攻击者通过伪装成合法用户来访问车辆的车载信息系统。",
        "攻击者通过伪装成车辆的控制单元来发送虚假的CAN总线消息。",
        "攻击者通过伪装成车辆的传感器来欺骗高级辅助驾驶系统（ADAS）。",
        "攻击者通过伪装成车辆的合法用户来获取车辆的远程控制权限。",
        "攻击者通过伪装成车辆的合法通信设备来获取车辆的诊断信息。",
        "攻击者通过伪装成车辆的合法固件来实施恶意更新。",
        "攻击者通过伪装成车辆的合法服务提供商来获取车辆的敏感数据。",
        "攻击者通过伪装成车辆的合法用户来获取车辆的实时位置和历史轨迹信息。"
    ],
    '篡改': [
        "攻击者通过篡改车辆的CAN总线消息来控制车辆的制动系统。",
        "攻击者通过篡改车辆的固件来植入恶意代码。",
        "攻击者通过篡改车辆的传感器数据来欺骗ADAS系统。",
        "攻击者通过篡改车辆的车载信息系统来显示错误的导航信息。",
        "攻击者通过篡改车辆的OTA升级过程来植入恶意固件。",
        "攻击者通过篡改车辆的车载诊断（OBD）系统来伪造车辆状态。",
        "攻击者通过篡改车辆的充电网络系统来实施免费充电。",
        "攻击者通过篡改车辆的车载信息服务（TSP）来获取车辆的敏感信息。",
        "攻击者通过篡改车辆的移动应用程序来实施远程控制。",
        "攻击者通过篡改车辆的Wi-Fi网络设置来实施中间人攻击。"
    ],
    '抵赖': [
        "攻击者在未授权访问车辆系统后，否认其行为，导致无法追踪责任。",
        "攻击者在篡改车辆数据后，否认其行为，导致数据完整性无法验证。",
        "攻击者在实施拒绝服务攻击后，否认其行为，导致服务中断无法归因。",
        "攻击者在提升权限后，否认其行为，导致权限变更无法追踪。",
        # 由于否认类型的威胁场景较少，这里只列出了四种，可以根据实际情况补充更多。
    ],
    '信息泄露': [
        "攻击者通过未加密的通信渠道窃取车辆的敏感数据。",
        "攻击者通过未授权访问车辆的车载信息系统来获取用户数据。",
        "攻击者通过漏洞利用获取车辆的诊断信息和个人数据。",
        "攻击者通过未授权访问车辆的移动应用程序来获取用户的位置信息。",
        "攻击者通过未授权访问车辆的车载信息服务（TSP）来获取车辆的实时位置。",
        "攻击者通过未授权访问车辆的充电网络系统来获取充电数据。",
        "攻击者通过未授权访问车辆的OTA升级服务器来获取固件更新数据。",
        "攻击者通过未授权访问车辆的OBD-II端口来获取车辆的诊断信息。",
        "攻击者通过未授权访问车辆的Wi-Fi网络来获取网络流量数据。",
        "攻击者通过未授权访问车辆的蓝牙系统来获取通信数据。"
    ],
    '拒绝服务': [
        "攻击者通过发送大量请求来耗尽车辆的网络资源，导致车辆服务不可用。",
        "攻击者通过发送恶意数据包来破坏车辆的网络通信，导致服务中断。",
        "攻击者通过破坏车辆的车载信息系统来阻止用户访问服务。",
        "攻击者通过破坏车辆的OTA升级过程来阻止软件更新。",
        "攻击者通过破坏车辆的充电网络系统来阻止电动汽车充电。",
        "攻击者通过破坏车辆的车载诊断（OBD）系统来阻止车辆诊断。",
        "攻击者通过破坏车辆的移动应用程序来阻止用户控制车辆。",
        "攻击者通过破坏车辆的Wi-Fi网络来阻止用户连接互联网。",
        "攻击者通过破坏车辆的蓝牙系统来阻止用户进行无线通信。",
        "攻击者通过破坏车辆的传感器系统来阻止ADAS功能正常工作。"
    ],
    '权限提升': [
        "攻击者通过漏洞利用提升权限，获取车辆的控制权。",
        "攻击者通过社会工程学手段提升权限，获取车辆的敏感操作权限。",
        "攻击者通过未授权访问车辆的车载信息系统来提升权限。",
        "攻击者通过未授权访问车辆的OTA升级服务器来提升权限。",
        "攻击者通过未授权访问车辆的OBD-II端口来提升权限。",
        "攻击者通过未授权访问车辆的移动应用程序来提升权限。",
        "攻击者通过未授权访问车辆的车载诊断（OBD）系统来提升权限。",
        "攻击者通过未授权访问车辆的充电网络系统来提升权限。",
        "攻击者通过未授权访问车辆的车载信息服务（TSP）来提升权限。",
        "攻击者通过未授权访问车辆的Wi-Fi网络来提升权限。"
    ]
}
# 定义每个威胁类型的攻击路径列表
attack_paths = {
    '欺骗': [
        "盗取或猜测密码",
        "利用钓鱼攻击获取凭证",
        "使用中间人攻击（MITM）来截取和伪造身份",
        "利用会话劫持攻击",
        "利用跨站点请求伪造（CSRF）攻击",
        "利用跨站点脚本（XSS）攻击来窃取会话令牌",
        "利用弱或无认证机制",
        "利用未加密的通信渠道进行身份盗窃",
        "利用社会工程学获取敏感信息",
        "利用操作系统或应用程序漏洞进行身份伪装",
        "利用弱或默认配置进行身份冒充",
        "利用未更新的证书和密钥管理实践",
        "利用双因素认证（2FA）绕过技术",
        "利用生物识别系统的缺陷进行身份冒充",
        "利用无意识的内部人员泄露的凭证信息"
    ],
    '篡改': [
        "利用SQL注入攻击修改数据库内容",
        "利用跨站点脚本（XSS）攻击篡改网页内容",
        "利用本地或远程文件包含（LFI/RFI）攻击篡改数据",
        "利用缓冲区溢出攻击修改内存数据",
        "利用未加密的通信渠道篡改传输数据",
        "利用会话固定攻击篡改会话令牌",
        "利用不安全的API端点篡改数据",
        "利用不安全的反序列化篡改对象状态",
        "利用不安全的配置篡改系统设置",
        "利用未验证的输入篡改应用程序逻辑",
        "利用弱加密算法篡改加密数据",
        "利用未签名的代码篡改软件行为",
        "利用弱访问控制篡改文件和目录",
        "利用未加密的备份篡改数据",
        "利用未修补的软件漏洞篡改系统状态"
    ],
    '抵赖': [
        "缺乏足够的审计日志记录",
        "攻击者删除或修改日志文件",
        "使用代理服务器或VPN隐藏真实身份",
        "使用Tor网络或其他匿名服务进行攻击",
        "利用未加密的通信渠道进行否认",
        "利用弱或无非否认机制",
        "利用未签名的交易进行否认",
        "利用未认证的通信渠道进行否认",
        "利用未授权的操作进行否认",
        "利用未追踪的系统访问进行否认",
        "利用未记录的系统更改进行否认",
        "利用未监控的系统活动进行否认",
        "利用未绑定到身份的操作进行否认",
        "利用未记录的异常行为进行否认",
        "利用未追踪的系统访问模式进行否认"
    ],
    '信息泄露': [
        "利用未加密的通信渠道泄露敏感信息",
        "利用未加密的存储泄露敏感数据",
        "利用配置不当的访问控制泄露数据",
        "利用未授权的API端点泄露信息",
        "利用跨站点脚本（XSS）攻击泄露会话信息",
        "利用SQL注入攻击泄露数据库内容",
        "利用未修补的软件漏洞泄露系统信息",
        "利用弱认证机制泄露凭证信息",
        "利用未监控的系统访问泄露敏感数据",
        "利用未限制的数据导出功能泄露信息",
        "利用未限制的文件上传功能泄露信息",
        "利用未限制的远程访问泄露内部网络信息",
        "利用未加密的备份泄露敏感数据",
        "利用未限制的调试信息泄露系统细节",
        "利用未限制的错误消息泄露敏感信息"
    ],
    '拒绝服务': [
        "利用分布式拒绝服务（DDoS）攻击使服务不可用",
        "利用资源耗尽攻击使系统无法处理合法请求",
        "利用服务拒绝攻击（DoS）使特定服务崩溃",
        "利用带宽耗尽攻击使网络服务不可用",
        "利用反射攻击使网络流量过载",
        "利用放大攻击使网络流量异常增加",
        "利用未限制的请求速率使服务过载",
        "利用未限制的并发连接使服务不可用",
        "利用未限制的数据处理使服务崩溃",
        "利用未限制的输入大小使服务拒绝",
        "利用未限制的数据处理使服务资源耗尽",
        "利用未监控的系统活动使服务不可用",
        "利用未限制的数据处理使服务响应时间延长",
        "利用未限制的数据处理使服务崩溃",
        "利用未监控的系统资源使用使服务不可用"
    ],
    '权限提升': [
        "利用操作系统或应用程序漏洞提升权限",
        "利用未修补的软件漏洞进行权限提升",
        "利用未限制的文件系统访问进行权限提升",
        "利用未限制的进程访问进行权限提升",
        "利用未限制的系统配置更改进行权限提升",
        "利用未限制的服务访问进行权限提升",
        "利用未限制的API调用进行权限提升",
        "利用未限制的系统命令执行进行权限提升",
        "利用未限制的数据库操作进行权限提升",
        "利用未限制的网络访问进行权限提升",
        "利用未限制的系统资源访问进行权限提升",
        "利用未限制的系统服务访问进行权限提升",
        "利用未限制的系统进程访问进行权限提升",
        "利用未限制的系统资源管理进行权限提升",
        "利用未限制的系统用户管理进行权限提升",
        "利用未限制的系统权限管理进行权限提升"
    ]
}

def get_risk_level(threat_level, impact_level):
    # 定义风险等级和威胁等级
    threat_levels = ["非常低", "低", "中", "高"]
    impact_levels = ["无明显影响的", "中等的", "重大的", "严重的"]

    # 创建二维数组来存储风险等级评级
    risk_rating_matrix = [
        [1, 1, 2, 3],  # Very Low Threat Level
        [1, 2, 3, 4],  # Low Threat Level
        [2, 3, 4, 4],  # Medium Threat Level
        [3, 4, 4, 5]  # High Threat Level
    ]
    threat_index = threat_levels.index(threat_level)
    impact_index = impact_levels.index(impact_level)

    return risk_rating_matrix[threat_index][impact_index]

# 用二维数组 存储 影响等级映射矩阵
impact_mapping_matrix = [
    [1, 1, 1, 1],  # 可忽略不计的
    [1, 2, 2, 2],  # 中等的
    [1, 2, 3, 4],  # 重大的
    [1, 3, 4, 4]   # 严重的
]
save_path = 'impact_mapping_matrix.csv'

def get_impact_level(item_level, ecu_level):
    # 定义风险等级和威胁等级
    item_levels = ["可忽略不计的", "中等的", "重大的", "严重的"]
    ecu_levels = ["Negligible", "Moderate", "Major", "Severe"]

    item_index = item_levels.index(item_level)
    ecu_index = ecu_levels.index(ecu_level)

    return impact_mapping_matrix[item_index][ecu_index]

# 导入 EcuFeatureTable
def create_ecu_feature_table(pid, file_path, sheet_name):
    # 读取Excel文件
    df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=0)

    EcuFeatureTable.query.delete() # 清空表项

    for index, row in df.iterrows():
        # index_str = f"{int(index) + 1:02d}"  # 使用 f-string 格式化，index + 1 是因为索引从0开始
        # name = f"{index_str}_{row['name']}"  # 在 name 前面加上两位数的序列号
        name = row['name']
        type = row['type']
        remark = row['remark']

        r = EcuFeatureTable(feature=name, type=type, remark=remark if remark else '')
        db.session.add(r)
    db.session.commit()

# import  FeatureMapSecurityAttrTable
def create_feature_map_security_attr_table(pid, file_path, sheet_name):
    df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=0)
    count = 0
    for index, row in df.iterrows():
        feature = row['特性']
        result = EcuFeatureTable.query.filter_by(feature=feature).first()
        # result = EcuFeatureTable.query.filter(EcuFeatureTable.feature.like(f'___{feature}')).first()

        if result:
            f_id = result.id
            security_attrs = ['可用性', '完整性', '机密性', '真实性', '不可抵赖性', '身份授权', '隐私']
            for attr in security_attrs:
                if row[attr] == '√' or 'Concept' in str(row[attr]):
                    security_attr = SecurityAttrTable.query.filter_by(name=attr).first()
                    if security_attr:
                        r = FeatureMapSecurityAttrTable(feature_id=f_id, security_attr_id=security_attr.id, req_name=str(row[attr]).strip())
                        db.session.add(r)
                        # db.session.commit()
                        count += 1
                        c=FeatureMapSecurityAttrTable.query.count()
                        # print(f'FeatureMapSecurityAttrTable : {c}')
        else:
            print(f'Error: 无法识别 {feature}')
    db.session.commit()
    return count

# import  ThreadAssessmentParaTable
def create_thread_assessment_para_table(pid, file_path, sheet_name):
    df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=0)
    for index, row in df.iterrows():
        name = row['name']
        value = row['value']
        type = row['type']
        standard = row['standard']
        r = ThreadAssessmentParaTable(name=name, value=value, type=type, standard=standard)
        db.session.add(r)
    db.session.commit()

# import  RiskAssessmentParaTable
def create_risk_assessment_para_table(pid, file_path, sheet_name):
    df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=0)
    for index, row in df.iterrows():
        name = row['name']
        explain = row['explain']
        value = row['value']
        type = row['type']
        standard = row['standard']
        r = RiskAssessmentParaTable(name=name, explain=explain, value=value, type=type, standard=standard)
        db.session.add(r)
    db.session.commit()

#

def import_tara_rule_mapping_table(pid, file_path, sheet_name):
    global impact_mapping_matrix
    global save_path
    df = pd.read_excel(file_path, sheet_name='表3-影响矩阵', header=None)

    # print(impact_mapping_matrix)
    impact_mapping_matrix = df.iloc[2:6, 1:5].values.tolist()
    pd.DataFrame(impact_mapping_matrix).to_csv(save_path, index=False, header=False)
    # print(impact_mapping_matrix)

    AssetScenarioDescTable.query.delete()
    AssetRiskScenarioAssessmentTable.query.delete()
    db.session.commit()

    # 1
    df = pd.read_excel(file_path, sheet_name='表1-资产危害场景评估规则表', skiprows=2)
    # df = pd.read_excel(file_path, sheet_name='表1', skiprows=2)
    _asset='#'
    _safety_attr='#'
    _damage_scenario='#'
    _remark='#'
    # print(f' # 1 #')
    for index, row in df.iterrows():
        # 检查是否为 NaN
        if not pd.isna(row.iloc[1]):
            asset = row.iloc[1]
            _asset = asset
        else:
            asset = _asset

        safety_attr = row.iloc[2] if not pd.isna(row.iloc[2]) else _safety_attr
        damage_scenario_desc = row.iloc[3] if not pd.isna(row.iloc[3]) else _damage_scenario
        remark = row.iloc[4] if not pd.isna(row.iloc[4]) else _remark
        item_level = row.iloc[5]

        _safety_attr = safety_attr
        _damage_scenario = damage_scenario_desc
        _remark = remark

        # print(f'={index}= {asset}, {safety_attr}, {damage_scenario_desc}, {remark}')

        ecu_feature = EcuFeatureTable.query.filter_by(feature=asset).first()
        sa = SecurityAttrTable.query.filter_by(name=safety_attr).first()
        if ecu_feature and sa:
            result = AssetScenarioDescTable.query.filter_by(ecu_feature_id=ecu_feature.id, security_attr_id=sa.id).first()
            print(f'result={result}, ecu_feature_id={ecu_feature.id}, security_attr_id={sa.id}')
            if result is None:
                record = AssetScenarioDescTable(ecu_feature_id=ecu_feature.id,
                                                security_attr_id=sa.id,
                                                damage_scenario_desc=damage_scenario_desc,
                                                remark=remark)
                db.session.add(record)
                db.session.commit()

            if item_level :
                # item_level = str(item_level).strip()
                out_level = f'{row["级别(S)"]},{row["级别(F)"]},{row["级别(O)"]},{row["级别(P)"]}'
                record = AssetRiskScenarioAssessmentTable(ecu_feature_id=ecu_feature.id, security_attr_id=sa.id,
                                                          type='sfop',
                                                          level=str(item_level),
                                                          out_level=out_level)
                db.session.add(record)
                # db.session.commit()
            else:
                print(f'-1- Error: item_level {type(item_level)} {item_level}, {index}')

        else:
            print(f'-1- Error: 非法 {asset}, {safety_attr}')

    # 2
    df = pd.read_excel(file_path, sheet_name='表2-ECU威胁评估标准', skiprows=2)
    _asset = '#'
    _safety_attr = '#'
    _attack_path = '#'
    _damage_scenario = '#'
    for index, row in df.iterrows():
        # 检查是否为 NaN
        if not pd.isna(row.iloc[1]):
            asset = row.iloc[1]
            _asset = asset
        else:
            asset = _asset

        safety_attr = row.iloc[2] if not pd.isna(row.iloc[2]) else _safety_attr
        _safety_attr = safety_attr

        person = row.iloc[0]
        thread_scenario = row.iloc[3]
        attack_path = row.iloc[4]
        ecu_attack_level=row.iloc[5]

        ecu_feature = EcuFeatureTable.query.filter_by(feature=asset).first()
        sa = SecurityAttrTable.query.filter_by(name=safety_attr).first()
        print(f'-{index}- ECU攻击向量： {ecu_attack_level} ')
        if ecu_feature and sa:
            result = AssetScenarioDescTable.query.filter_by(ecu_feature_id=ecu_feature.id,
                                                            security_attr_id=sa.id).first()
            if result:
                if pd.isna(row.iloc[2]):
                    print(f'row.iloc[2]: {safety_attr}')
                else:
                    result.thread_scenario=thread_scenario
                    result.attack_path=attack_path
                    result.person=person

                # print(f'_Found_. {result.ecu_feature_id},{result.security_attr_id} attack_path={result.attack_path}')
            else:
                print(f'-2- Error: not found {asset}, {safety_attr}')

            out_level = f'{row["列举值(1)"]},{row["列举值(2)"]},{row["列举值(3)"]},{row["列举值(4)"]},{row["列举值(5)"]}'
            # print(f'威胁场景评估：{out_level}')
            record = AssetRiskScenarioAssessmentTable(ecu_feature_id=ecu_feature.id, security_attr_id=sa.id,
                                                      type='ecu攻击向量',
                                                      level=ecu_attack_level,
                                                      out_level=out_level)
            db.session.add(record)

    db.session.commit()


def read_impact_mapping_matrix_from_csv():
    global impact_mapping_matrix
    global save_path

    if os.path.exists(save_path):
        df = pd.read_csv(save_path, header=None)
        impact_mapping_matrix = df.values.tolist()
        print(impact_mapping_matrix)
    else:
        print(f'file not found: {save_path}')

def import_item_impact_table(pid, file_path, sheet_name):
    ItemTable.query.filter_by(project_id=0).delete(synchronize_session=False)
    df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=1)
    count = 0
    for index, row in df.iterrows():
        system = row['功能域']
        name = row['ITEM']
        number = row['ITEM编号']
        desc = row['描述']
        safety_impact_level = row['影响级别(S)']
        safety_impact_desc = row['描述(S)']
        economic_impact_level = row['影响级别(F)']
        economic_impact_desc = row['描述(F)']
        operation_impact_level = row['影响级别(O)']
        operation_impact_desc = row['描述(O)']
        privacy_impact_level = row['影响级别(P)']
        privacy_impact_desc = row['描述(P)']
        r = ItemTable(name=name, system=system, number = number, desc = desc,
                      safety_impact_level=safety_impact_level, safety_impact_desc=safety_impact_desc,
                      economic_impact_level=economic_impact_level, economic_impact_desc=economic_impact_desc,
                      operation_impact_level=operation_impact_level, operation_impact_desc=operation_impact_desc,
                      privacy_impact_level=privacy_impact_level, privacy_impact_desc=privacy_impact_desc,
                      project_id=0)
        db.session.add(r)
        count += 1
    db.session.commit()
    return count

# import  EcuTable
def create_ecu_table_new(pid, file_path, sheet_name):
    # 读取Excel文件
    print(f'导入 ECU表: {file_path}, {sheet_name}, {pid}')
    df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=2)

    # 定义需要检查的列名
    required_columns = {'零部件名称', '零部件类型', '芯片',  '第三方应用','重要数据', '个人数据', '关键数据' }
    # 检查是否存在所有需要的列
    if not required_columns.issubset(df.columns):
        missing_columns = required_columns - set(df.columns)
        raise ValueError(f"Excel 表格格式错误: 缺少以下列 [ {', '.join(missing_columns)} ]")

    # 清空相关的表项 EcuTable , EcuMapFeatureTable
    EcuTable.query.filter_by(Project_id=pid).delete(synchronize_session=False)
    EcuMapFeatureTable.query.filter_by(project_id=pid).delete(synchronize_session=False)
    db.session.commit()

    for index, row in df.iterrows():
        name = row['零部件名称']
        abbr = str(row['零部件类型']).strip()
        abbr_ext = str(row['零部件缩写']).strip()
        pcb = row['PCB']
        chipset = row['芯片']
        os = str(row['通用操作系统'])
        Bootloader = row['Bootloader']
        Linux = '√' if os.upper() == 'LINUX' else ''
        Windows = '√' if os.upper() == 'WINDOWS' else ''
        Android = '√' if os.upper() == 'ANDROID' else ''
        QNX = '√' if os.upper() == 'QNX' else ''
        HarmonyOS = '√' if os.upper() == 'HARMONYOS' else ''
        RTOS = '√' if os.upper() == 'RTOS' else ''

        app = row['OEM原生应用']
        app_3rd=row['第三方应用']

        data_1 =row['重要数据']
        data_2 =row['个人数据']
        data_3 =row['关键数据']
        data_key = row['密钥']  #
        data_car_info = row['车辆识别数据']
        data_log = row['日志']
        data_export = row['数据外发']
        data_cross_border = row['数据跨境']

        interface_usb = row['USB接口']
        interface_sd = row['SD卡接口']
        interface_debug = row['调试接口']

        can_bus = row['CAN通信']
        eth_bus = row['以太通信']
        flex_ray_bus = row['FlexRay通信']
        lin_bus =  row['LIN通信']
        wireless_bt = row['蓝牙']
        wireless_wifi = row['WIFI']
        wireless_rf = row['射频通信']
        wireless_mobile = row['蜂窝通信']

        wireless_gnss = row['GNSS']
        wireless_v2x = row['V2X']

        ota_upgrade = row['OTA']
        usb_upgrade = row['USB升级']
        obd_upgrade = row['OBD升级']
        obd_debug = row['OBD诊断']
        remote_debug =  row['远程诊断']  #
        other_remote_cmd =  row['其他远程控制指令']  #
        intrusion_detection_prevention_systems = row['IDPS']
        upgrade_node =row['车载升级主控节点']
        tbox = row['TBox'] # TBox
        gate_way =  row['网关']

        affect_level = row['影响级别']
        attack_type = row['攻击向量']
        cal_level = row['CAL等级']

        RP = row['ECU负责人']
        RP_department = row['ECU负责部门']
        phone1 = row['ECU负责人联系电话']
        email1 = row['ECU负责人联系邮箱']
        safety_contact_person = row['安全联系人']
        phone2 = row['安全联系人联系电话']
        email2 = row['安全联系人联系邮箱']
        vendor = row['供应商名称']
        vendor_person = row['供应商联系人']
        vendor_phone = row['供应商联系电话']
        vendor_email = row['供应商联系邮箱']

        # 创建 EcuTable 对象并插入数据库
        ecu = EcuTable(
            name=name,
            abbr=abbr,
            abbr_ext=abbr_ext,
            Project_id=pid,
            pcb=pcb,
            chipset=chipset,
            Bootloader=Bootloader,
            Linux=Linux,
            Windows=Windows,
            Android=Android,
            QNX=QNX,
            HarmonyOS=HarmonyOS,
            RTOS=RTOS,
            OS=os if os != 'nan' else '',

            app=app,
            app_3rd=app_3rd,
            data_1=data_1,
            data_2=data_2,
            data_3=data_3,
            data_key=data_key,
            data_car_info = data_car_info,
            data_log = data_log,
            data_export = data_export,
            data_cross_border = data_cross_border,
            interface_usb=interface_usb,
            interface_sd=interface_sd,
            interface_debug=interface_debug,
            can_bus=can_bus,
            eth_bus=eth_bus,
            wireless_bt=wireless_bt,
            wireless_wifi=wireless_wifi,
            wireless_rf=wireless_rf,
            wireless_mobile=wireless_mobile,
            wireless_gnss=wireless_gnss,
            wireless_v2x=wireless_v2x,
            ota_upgrade=ota_upgrade,
            usb_upgrade=usb_upgrade,
            obd_upgrade=obd_upgrade,
            obd_debug=obd_debug,
            flex_ray_bus=flex_ray_bus,
            lin_bus = lin_bus,
            remote_debug=remote_debug,
            other_remote_cmd = other_remote_cmd,
            upgrade_node = upgrade_node,
            tbox = tbox,
            gate_way = gate_way,
            intrusion_detection_prevention_systems=intrusion_detection_prevention_systems,
            affect_level=affect_level,
            attack_type = attack_type,
            cal_level = cal_level,

            RP=RP,
            RP_department=RP_department,
            phone1=phone1,
            email1=email1,
            safety_contact_person=safety_contact_person,
            phone2=phone2,
            email2=email2,
            vendor=vendor,
            vendor_person=vendor_person,
            vendor_phone=vendor_phone,
            vendor_email=vendor_email
        )
        db.session.add(ecu)
        db.session.commit()
        # print(f'>>> ecu id: {ecu.id},  name: {ecu.name}, {ecu.abbr}')
        create_ecu_map_feature_table(ecu, pid)

    return EcuTable.query.filter_by(Project_id=pid).count()

def del_ecu_map_feature_table(ecu, pid):
    EcuMapFeatureTable.query.filter_by(ecu_id=ecu.id, project_id=pid).delete()
    db.session.commit()

def create_ecu_map_feature_table(ecu, pid):
    field_mapping = {
        'PCB': 'pcb',
        '芯片': 'chipset',
        'Bootloader': 'Bootloader',
        '通用操作系统': 'OS',
        'OEM原生应用': 'app',
        '第三方应用': 'app_3rd',
        '重要数据': 'data_1',
        '个人数据': 'data_2',
        '关键数据': 'data_3',
        '密钥': 'data_key',
        '车辆识别数据': 'data_car_info',
        '日志': 'data_log',
        '数据外发': 'data_export',
        '数据跨境': 'data_cross_border',
        'USB接口': 'interface_usb',
        'SD卡接口': 'interface_sd',
        '调试接口': 'interface_debug',
        'CAN通信': 'can_bus',
        '以太通信': 'eth_bus',
        'FlexRay通信': 'flex_ray_bus',
        'LIN通信': 'lin_bus',
        '蓝牙': 'wireless_bt',
        'WIFI': 'wireless_wifi',
        '射频通信': 'wireless_rf',
        '蜂窝通信': 'wireless_mobile',
        'GNSS': 'wireless_gnss',
        'V2X': 'wireless_v2x',
        'OTA': 'ota_upgrade',
        'USB升级': 'usb_upgrade',
        'OBD升级': 'obd_upgrade',
        'OBD诊断': 'obd_debug',
        '远程诊断': 'remote_debug',
        '其他远程控制指令': 'other_remote_cmd',
        'IDPS': 'intrusion_detection_prevention_systems',
        '车载升级主控节点': 'upgrade_node',
        'TBox': 'tbox',
        '网关': 'gate_way'
    }

    features = EcuFeatureTable.query.all()
    for feature in features:
        try:
            # 获取对应的数据库字段名
            field_name = field_mapping.get(feature.feature)
            if field_name:
                val = getattr(ecu, field_name, '')
            else:
                val = ''

            if isinstance(val, str) and '√' in val:
                map_record = EcuMapFeatureTable(
                    ecu_id=ecu.id, 
                    feature_id=feature.id,
                    os=f'{ecu.id}:{ecu.abbr} -> {feature.feature}',
                    project_id=pid
                )
                db.session.add(map_record)
                print(f'new map: {ecu.id}:{ecu.abbr} -> {feature.feature}')
        except KeyError:
            print(f'>> Column "{feature.feature}" not found in Sheet.')
            continue

    if ecu.OS and isinstance(ecu.OS, str):
        input_systems = [word.strip() for word in re.split(r'[/;,&]', ecu.OS)]
        print(f'操作系统 input_systems: "{ecu.OS}" => {input_systems}')
        for input_system in input_systems:
            if input_system == '':
                continue
            os_lists = EcuFeatureTable.query.filter_by(type='系统软件').all()
            # print(f'strIn={ecu.OS}, OSs={os_lists}')
            for sys in os_lists:
                if input_system.lower() == sys.feature.strip().lower():
                    r = EcuMapFeatureTable(ecu_id=ecu.id, feature_id=sys.id,
                                           os=f'{ecu.id}:{ecu.abbr} -> {sys.feature}',
                                           project_id=pid)
                    print(f'new map system: {ecu.id}:{ecu.abbr} -> {sys.id}: {sys.feature}')
                    db.session.add(r)
    db.session.commit()

# import  EcuTable 废弃
def create_ecu_table(pid, file_path, sheet_name):
    # 读取Excel文件
    df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=0)
    # 清空相关的表项 EcuTable , EcuMapFeatureTable
    EcuTable.query.filter_by(Project_id=pid).delete(synchronize_session=False)
    EcuMapFeatureTable.query.filter_by(project_id=pid).delete(synchronize_session=False)
    # EcuMapFeatureTable.query.delete(synchronize_session=False)
    db.session.commit()
    count = EcuMapFeatureTable.query.count()
    print(f"清空 EcuMapFeatureTable 表:< {count} >")

    for index, row in df.iterrows():
        # print(f'EcuTable(), index={index}, row={row}')
        name = row['零部件名称']
        abbr = str(row['零部件类型']).strip()
        RP = row['ECU负责人']
        RP_department = row['ECU负责部门']
        r = EcuTable(name=name, abbr=abbr, RP=RP, RP_department=RP_department, Project_id=pid)
        db.session.add(r)

        features = EcuFeatureTable.query.all()
        for feature in features:
            try:
                val = row[feature.feature]
                if isinstance(val, str) and val.strip().upper()  in ['√', '是', 'Y', 'YES']:
                    r = EcuMapFeatureTable(ecu_id=index + 1, feature_id=feature.id,
                                           os=f'{index + 1}:{abbr} -> {feature.feature}',
                                           project_id=pid)
                    db.session.add(r)
            except KeyError:
                print(f'>> Column "{feature.feature}" not found in Sheet.')
                continue

        if row['智能终端操作系统']:
            strIn = row['智能终端操作系统']
            # print(f'智能终端操作系统 : {strIn}, isString={isinstance(strIn, str)}')
            if isinstance(strIn, str):
                strIn = strIn.strip().lower()  # 去除前后空格并转换为小写
                oslists = EcuFeatureTable.query.filter_by(type='系统软件')
                print(f'strIn={strIn}, oss={oslists}')
                for sys in oslists:
                    if strIn == sys.feature.strip().lower():
                        r = EcuMapFeatureTable(ecu_id=index + 1, feature_id=sys.id,
                                               os=f'{index + 1}:{abbr} -> {sys.feature}',
                                               project_id=pid)
                        # print(f'new record={index + 1}, {sys.id}, {r}')
                        db.session.add(r)
    db.session.commit()
    count = EcuMapFeatureTable.query.count()
    print(f"EcuMapFeatureTable 表总数量: {count}")


# 生成 08 功能相关性表 FuncRelationTable
def create_func_relation_table(pid, file_path, sheet_name):
    # 读取Excel文件
    df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=1)
    # 清空相关的表项
    FuncRelationTable.query.filter_by(project_id=pid).delete(synchronize_session=False)
    db.session.commit()

    new_count = 0
    for index, row in df.iterrows():
        item = row['Item定义']
    #    if item is None or not isinstance(item, str) or len(item) <2:
    #        print(f'>> {index} : 非法 Item {item} ')
    #        continue

        system = row['整车功能域']
        f_id = row['功能ID']
        data_flow = format_data_flow(row['基本数据流'])
        if data_flow is None or not isinstance(data_flow, str):
            data_flow = ''

        # try:
        name = row['功能名']
        if name is None or not isinstance(name, str):
            continue
        desc = row['功能说明'] if row['功能说明'] else ''

        check = check_data_flow(data_flow, EcuTable, pid)
        print(f'{f_id}: {data_flow} ===> {check}')
        contact_person = row['责任人']
        bool_ee = row['对象是否为电子电气EE架构下的组件（内部通过各种总线通信）']
        bool_ee = 'Y'  if isinstance(bool_ee, str) and 'Y' in bool_ee.upper() else 'N'

        bool_safe = row['对象是否会影响车辆功能安全相关操作']
        bool_safe = 'Y' if isinstance(bool_safe, str) and 'Y' in bool_safe.upper() else 'N'

        bool_userdata = row['对象是否收集或处理用户或者敏感数据（参考附录1）']
        bool_userdata = 'Y' if isinstance(bool_userdata, str) and 'Y' in bool_userdata.upper() else 'N'

        bool_network = row['基于网络化的零部件实现车辆功能']
        bool_network = 'Y' if isinstance(bool_network, str) and 'Y' in bool_network.upper() else 'N'

        result = row['是否为网络安全相关项']
        result = 'Y' if isinstance(result, str) and 'Y' in result.upper() else 'N'

        # if bool_ee == 'Y' or bool_ee == 'YES':
        #     if bool_safe == 'Y' or bool_userdata == 'Y' or bool_network == 'Y' \
        #         or bool_safe == 'YES' or bool_userdata == 'YES' or bool_network == 'YES':
        #         result = 'Y'


        r = FuncRelationTable(name=name,  desc=desc, data_flow=data_flow, system=system,
                              check=check, func_id=f_id, item=item,
                              contact_person=contact_person, bool_ee=bool_ee, bool_safe=bool_safe,
                              bool_userdata=bool_userdata, bool_network=bool_network, result=result,
                              project_id=pid)
        new_count += 1
        db.session.add(r)
        print(f'<{f_id}> new func_relation_table: New func_relation({r}), pid={pid}')

        # except KeyError:
        #     print(f'** Error: {index} : {KeyError}.')
        #     continue

    db.session.commit()
    return new_count
def sync_item_table_plantuml(pid_source, pid_dest):
    print(backup_database())
    items = ItemTable.query.filter_by(project_id=pid_source).all()

    for item in items:
        dest_item = ItemTable.query.filter_by(name=item.name, project_id=pid_dest).first()
        if dest_item:
            if dest_item.plant_uml != item.plant_uml:
                print(f'sync plantUML: {item.plant_uml} \n {dest_item.plant_uml}')
                dest_item.plant_uml = item.plant_uml

        else:
            print(f'sync plantUML : not found ({item.name}, {pid_dest}).')



def update_item_table(pid):
    # ItemTable.query.filter_by(project_id=pid).delete(synchronize_session=False)
    ItemTable.query.filter_by(project_id=pid).update({ItemTable.del_flag: 1, ItemTable.desc: None, ItemTable.data_flow: None, ItemTable.ecu_list: None})
    db.session.commit()

    item_count = 0
    functions = FuncRelationTable.query.filter_by(project_id=pid).all()
    for f in functions:
        item = ItemTable.query.filter_by(name=f.item, project_id=pid).first()
        if item is None:
            item = ItemTable(name=f.item, system=f.system, desc=f.desc,
                             data_flow=f.data_flow, ecu_list=f.check,del_flag=2, project_id=pid)

            db.session.add(item)
            db.session.commit()

        f.func_domain_id = item.id
        if item.del_flag:
            # added 2025-02-04
            item_base = ItemTable.query.filter_by(name=item.name, project_id=0).first()
            if item_base:
                print(
                    f'-- Found: item({item_base.name}), {item_base.safety_impact_level}, {item_base.economic_impact_level}.')
                item.safety_impact_level = item_base.safety_impact_level
                item.safety_impact_desc = item_base.safety_impact_desc
                item.economic_impact_level = item_base.economic_impact_level
                item.economic_impact_desc = item_base.economic_impact_desc
                item.operation_impact_level = item_base.operation_impact_level
                item.operation_impact_desc = item_base.operation_impact_desc
                item.privacy_impact_level = item_base.privacy_impact_level
                item.privacy_impact_desc = item_base.privacy_impact_desc
            else:
                print(f'-- Error: item({item.name}) not found.')

        item.del_flag = 0  # 命中的条目不删除
        # print(f' desc={f.desc} || {item.desc}')
        if f.desc and f.desc != 'nan':
            if item.desc is None:
                item.desc = f.desc
            else:
                item.desc += "\n" + str(f.desc)

        # print(f' data_flow={f.data_flow} || {item.data_flow}')
        if f.data_flow and len(f.data_flow):
            if item.data_flow is None:
                item.data_flow = f.data_flow
            else:
                item.data_flow = f'{item.data_flow};\n{f.data_flow}'


        if f.result in ['Y','YES']:
            item.ecu_list = merge_string(item.ecu_list, f.check)
            # print(f'merged ECU list: {item.ecu_list}')


        db.session.commit()

    ItemTable.query.filter_by(del_flag=1).delete()

    uml_path = f'static/plantuml/{pid}'
    if not os.path.exists(uml_path):
        os.makedirs(uml_path)

    items = ItemTable.query.filter_by(project_id=pid).all()
    for item in items:
        item_count += 1
        item.number = f'{item_count:03d}'

    db.session.commit()
    return item_count


def get_threat_analysis_from_cache(ecu, ecu_abbr, feature, sa_name, thread_type):
    result = ThreatAnalysisShareTable.query.filter_by(ecu_abbr=ecu_abbr,
                                                      feature_name=feature,
                                                      security_attr_name=sa_name,
                                                      thread_type_name=thread_type).first()
    if result:
        if result.attack_path and len(result.attack_path) > 5:
            # print(f'get_threat_analysis_from_cache: founded.')
            return result
        else:
            print(f'get_threat_analysis_from_cache: 空表项, 待处理.')
            return threat_analysis_table_process(result, 0.5) #处理空表项

    print(f'get_threat_analysis_from_cache: not founded. {ecu_abbr}, {feature}, {sa_name}, {thread_type}')
    cache = ThreatAnalysisShareTable(
                ecu_name=ecu,
                ecu_abbr=ecu_abbr,
                feature_name=feature,
                security_attr_name=sa_name,
                thread_type_name=thread_type,
                attack_value = 0, attack_level = '低',
                attack_path_number='经验库-攻击路径编号', thread_scenario_number='经验库-威胁场景编号')
    db.session.add(cache)
    db.session.commit()
    return threat_analysis_table_process(cache, 0.5)

def auto_process_threat_analysis():
    results = ThreatAnalysisShareTable.query.all()
    ret = 0
    print(f'ThreatAnalysisShare经验库数量: {len(results)}')

    if len(results) > 0:
        results = ThreatAnalysisShareTable.query.filter_by(attack_path=None).all()
        if results:
            print(f'ThreatAnalysisShareTable:   发现 {len(results)} 条待处理表项。')
            index = 0
            ret = len(results)
            for r in results:
                index += 1
                ret = threat_analysis_table_process(r, 0.5)
                if ret:
                    print(f' Auto process ThreatAnalysisTable:<{ret.id}> {index}/{len(results)}  -  {ret.attack_path}')
    return ret

def auto_process_risk_assessment():
    results = RiskAssessmentShareTable.query.filter_by(damage_scenario_desc=None).all()
    count = 0
    if results is None:
        print(f'ThreatAnalysisShareTable 没有空表项要处理.')
        return count
    count = len(results)
    if len(results) < 1:
        print(f'RiskAssessmentShareTable: 发现 {len(results)} 条待处理表项.')
        return count
    else:
        print(f'RiskAssessmentShareTable: 找到 {len(results)} 条空表项, 开始自动处理...')

    index = 0
    for r in results:
        index += 1
        # if r.damage_scenario_desc and len(r.damage_scenario_desc) > 8:
        #     print(f'Skip RiskAssessmentEcuTable : {index:05d}')
        #     continue

        ret = risk_assessment_table_process(r, 0.5)
        db.session.commit()
        print(f'{ret.id}:  {index} / {len(results)} : {ret.item_name} - {ret.feature_name}')
    return count

def clear_tara_table(pid):
    RiskAssessmentEcuTable.query.filter_by(project_id=pid).delete(synchronize_session=False)
    ThreatAnalysisTable.query.filter_by(project_id=pid).delete(synchronize_session=False)

    db.session.commit()

# 同步最新的经验库数据到所有的 威胁分析表
def sync_threat_analysis_table():
    total = ThreatAnalysisTable.query.count()
    records = ThreatAnalysisTable.query.all()
    count = 0
    error_count = 0
    for r in records:
        count += 1

        if r.ecu is None or r.ecu_feature is None or r.security_attr is None:
            print(f'Error : sync_threat_analysis_table() 空指针 {r.ecu}, {r.ecu_feature}, {r.security_attr}')
            error_count += 1
            continue

        result = ThreatAnalysisShareTable.query.filter_by(
                ecu_abbr=r.ecu.abbr,
                feature_name=r.ecu_feature.feature,
                security_attr_name=r.security_attr.name
            ).first()
        print(f'sync_threat_analysis_table: {count}/{total}  {result}')
        if result:
            if r.attack_value != result.attack_value:
                r.attack_value = result.attack_value
            if r.attack_level != result.attack_level:
                r.attack_level = result.attack_level
            if r.attack_path != result.attack_path:
                r.attack_path = result.attack_path
            if r.thread_scenario != result.thread_scenario:
                r.thread_scenario = result.thread_scenario
            if r.vdt_id != result.vdt_id:
                r.vdt_id = result.vdt_id
            if r.asl_id != result.asl_id:
                r.asl_id = result.asl_id
            if r.knowledge_level_id != result.knowledge_level_id:
                r.knowledge_level_id = result.knowledge_level_id
            if r.woo_id != result.woo_id:
                r.woo_id = result.woo_id
            if r.device_id != result.device_id:
                r.device_id = result.device_id
        #
        # if count % 256 == 0:
        #     db.session.commit()
    db.session.commit()
    return f'完成威胁分析表同步:  total={total}, error={error_count}.'

# 生成 3.1 威胁分析表 ThreatAnalysisTable
def create_threat_analysis_table(pid):
    ecus = EcuTable.query.filter_by(Project_id=pid).all()
    if not ecus:
        # jsonify({'msg': '请先导入ECU表'})
        return '请先导入ECU表'

    record_count = 0
    ecu_count = EcuTable.query.filter_by(Project_id=pid).count()
    print(f'create_threat_analysis_table: ecu count: {ecu_count}')
    for ecu in ecus:
        print(f'create_threat_analysis_table: pid={pid}, ecu={ecu}')
        map_features = EcuMapFeatureTable.query.filter_by(project_id=pid, ecu_id=ecu.id).all()
        # print(f'--map_features: {map_features}')
        for map_feature in map_features:
            i = 1
            # print(f'---- map_feature: {map_feature.feature.feature}')
            for map_sa in map_feature.feature.feature_map_sas:  # map_sa.security_attr
                attack_path_number = f'AP-{ecu.abbr}-{map_feature.feature.feature}-{i:02d}'
                thread_scenario_number = f'T-{ecu.abbr}-{map_feature.feature.feature}-{i:02d}'
                # print(f'  thread_scenario_number = {thread_scenario_number}, 安全属性({map_sa.id}): {map_sa.security_attr.name}')
                thread_type = ThreadTypeTable.query.filter_by(security_attr=map_sa.security_attr.name).first()
                # print(f'  thread_type = {thread_type}')
                if thread_type:  # 3.1 威胁分析表

                    # cache = get_threat_analysis_from_cache(ecu.id, map_feature.feature.id, map_sa.security_attr.id, thread_type.id)
                    cache = get_threat_analysis_from_cache(ecu.name, ecu.abbr,
                                                           map_feature.feature.feature,
                                                           map_sa.security_attr.name,
                                                           thread_type.name)
                    r = ThreatAnalysisTable(ecu_id=ecu.id, ecu_feature_id=map_feature.feature.id,
                                              security_attr_id=map_sa.security_attr.id,
                                              thread_type_id=thread_type.id,
                                              thread_scenario_number=thread_scenario_number,
                                              attack_path_number=attack_path_number,
                                              attack_value=cache.attack_value,
                                              attack_level=cache.attack_level,
                                              attack_path= cache.attack_path, thread_scenario=cache.thread_scenario,
                                              vdt_id=cache.vdt_id, asl_id=cache.asl_id, knowledge_level_id=cache.knowledge_level_id, woo_id=cache.woo_id, device_id=cache.device_id,
                                              project_id=pid
                                              )
                    threat_analysis_table_rule_process(r, ecu, map_feature.feature, map_sa.security_attr)

                    db.session.add(r)
                    db.session.commit()
                    auto_calc_attack_level(r)
                    record_count += 1
                i = i + 1
    db.session.commit()
    c = ThreatAnalysisShareTable.query.count()
    print(f"威胁分析表({pid}) + {record_count} 条. 威胁分析表经验库: {c}")

    return '威胁分析表更新完成.'

def threat_analysis_table_rule_process(threat_analysis_table, ecu_table, feature, security_attr):

    values = AssetRiskScenarioAssessmentTable.query.filter_by(ecu_feature_id=feature.id,
                                                                  security_attr_id=security_attr.id,
                                                                  level=ecu_table.attack_type).first()
    if values is None or values.out_level is None:
        print(f'-- Error: threat_analysis_table_rule_process ({ecu_table.name}, {feature.feature})')
        return

    value_list = values.out_level.split(',')

    if len(value_list) != 5:
        print(f'-- Error: list：{value_list}  // {ecu_table.name} {feature.feature}')
    else:
        v1, v2, v3, v4, v5 = value_list

        p1 = ThreadAssessmentParaTable.query.filter_by(type='发现漏洞所需要的时间', name=v1).first()
        p2 = ThreadAssessmentParaTable.query.filter_by(type='攻击者专业水平', name=v2).first()
        p3 = ThreadAssessmentParaTable.query.filter_by(type='对目标的知识度水平', name=v3).first()
        p4 = ThreadAssessmentParaTable.query.filter_by(type='威胁被利用的机会窗口水平', name=v4).first()
        p5 = ThreadAssessmentParaTable.query.filter_by(type='攻击所需设备水平', name=v5).first()

        if p1 and p2 and p3 and p4 and p5:
            threat_analysis_table.vdt_id = p1.id
            threat_analysis_table.asl_id = p2.id
            threat_analysis_table.knowledge_level_id = p3.id
            threat_analysis_table.woo_id = p4.id
            threat_analysis_table.device_id = p5.id

        else:
            print(f'-= Error: ThreadAssessmentParaTable.query fail. {p1}[{v1}], {p2}, {p3}, {p4}, {p5}')


def get_risk_assessment_req(feature_id,security_attr_id , pid):
    """
    基于规则，设置TARA表项的安全需求
    """
    ret = None
    map_table = FeatureMapSecurityAttrTable.query.filter_by(feature_id=feature_id , security_attr_id=security_attr_id).first()
    if  map_table:
        ret = CybersecurityReqTable.query.filter_by(project_id=pid, code_or=map_table.req_name).first()
        # if ret:
        #     # print(f'map_table: {map_table.req_name} => {ret.id}, pid={pid}')
        # else:
        #     print(f'Error : get_risk_assessment_req not found: {map_table.req_name}, pid={pid}')

    return ret

def get_risk_assessment_from_cache(item_name, ecu, ecu_abbr, feature_name, sa_name):
    result = RiskAssessmentShareTable.query.filter_by(
                item_name=item_name,
                ecu_name=ecu,
                ecu_abbr=ecu_abbr,
                feature_name=feature_name,
                security_attr_name=sa_name).first()
    if result:
        if result.damage_scenario_desc and len(result.damage_scenario_desc) > 8:
            # print(f'==> 发现RiskAssessmentShareTable: OK')
            return result # 找到，直接返回
        else:
            # print(f'==> 发现RiskAssessmentShareTable空表项 :{result.damage_scenario_desc}, {item_name}, {ecu}, {ecu_abbr}, {feature_name}, {sa_name}')
            return risk_assessment_table_process(result, 0.5)


    tat = ThreatAnalysisShareTable.query.filter_by(
            ecu_abbr=ecu_abbr, feature_name=feature_name,
            security_attr_name=sa_name).first()
    if tat is None:
        print(f'  Error get_risk_assessment_from_cache: not found ThreatAnalysisTable: {ecu_abbr}, {feature_name}, {sa_name}')
        threat_analysis_id = None
    else:
        threat_analysis_id = tat.id

    cache = RiskAssessmentShareTable(
        item_name=item_name,
        item_desc=item_name,
        ecu_name=ecu,
        ecu_abbr=ecu_abbr,
        feature_name=feature_name,
        security_attr_name=sa_name,
        threat_analysis_id=threat_analysis_id,
        safe_affect_id=1,
        economy_affect_id=5,
        operator_affect_id=9,
        private_affect_id=13,

        affect_value=0,
        affect_level='无明显影响的',
        risk_level='1',
        risk_treatment_strategy_id=1
    )
    db.session.add(cache)
    db.session.commit()
    cache =  risk_assessment_table_process(cache, 0.5)


    return cache
def sync_all_tara_table():
    print(sync_threat_analysis_table())
    print(sync_risk_assessment_table())

    print(f'sync_all_tara_table: finished')

def sync_risk_assessment_table():
    total = RiskAssessmentEcuTable.query.count()
    count = 0
    error_count = 0

    records = RiskAssessmentEcuTable.query.all()
    for r in records:
        count += 1

        if r.item is None or r.ecu is None or r.security_attr is None or r.ecu_feature is None:
            print(f'Error : sync_risk_assessment_table(), {r.id}: {r.item}, {r.ecu}, {r.security_attr}, {r.ecu_feature}')
            error_count += 1
            db.session.delete(r)
            continue

        result = RiskAssessmentShareTable.query.filter_by(
                item_name=r.item.name,
                ecu_abbr=r.ecu.abbr,
                feature_name=r.ecu_feature.feature,
                security_attr_name=r.security_attr.name).first()

        print(f'sync_risk_table: {count}/{total}  - {result}')
        if result:
            if r.damage_scenario_desc != result.damage_scenario_desc:
                r.damage_scenario_desc = result.damage_scenario_desc
            if r.safe_affect_id != result.safe_affect_id:
                r.safe_affect_id = result.safe_affect_id
            if r.economy_affect_id != result.economy_affect_id:
                r.economy_affect_id = result.economy_affect_id
            if r.operator_affect_id != result.operator_affect_id:
                r.operator_affect_id = result.operator_affect_id
            if r.private_affect_id != result.private_affect_id:
                r.private_affect_id = result.private_affect_id
            if r.affect_value != result.affect_value:
                r.affect_value = result.affect_value
            if r.affect_level != result.affect_level:
                r.affect_level = result.affect_level
            if r.risk_level != result.risk_level:
                r.risk_level = result.risk_level
            if r.risk_treatment_strategy_id != result.risk_treatment_strategy_id:
                r.risk_treatment_strategy_id = result.risk_treatment_strategy_id

            cs_req = CybersecurityReqTable.query.filter_by(project_id=r.project_id, name_or=result.cybersecurity_req_name).first()
            if cs_req:
                if r.cybersecurity_req_id != cs_req.id:
                    r.cybersecurity_req_id = cs_req.id
            if r.r_attack_feasibility_level != result.r_attack_feasibility_level:
                r.r_attack_feasibility_level = result.r_attack_feasibility_level
            if r.r_risk_est_r_level != result.r_risk_est_r_level:
                r.r_risk_est_r_level = result.r_risk_est_r_level
            if r.r_risk_treatment_strategy_id != result.risk_treatment_strategy_id:
                r.r_risk_treatment_strategy_id = result.risk_treatment_strategy_id
        #
        # if  count % 256 == 0:
        #     db.session.commit()
    db.session.commit()
    return f'完成风险评估表同步:  total={total}, error={error_count}.'

# 生成 4.1 ECU级风险评估表
def create_risk_assessment_ecu_table(pid):
    new_count = 0
    items = ItemTable.query.filter_by(project_id=pid).all()
    # items = FuncRelationTable.query.filter_by(project_id=pid, result='Y').all()
    print(f"Number of items: {len(items)}")
    for item in items:
        ecu_list = item.ecu_list
        # print(f'{item.name} -> {ecu_list}')
        if ecu_list and len(ecu_list) > 1:
            ecus = ecu_list.split(',')
            # print(f'{item.name} :  {ecus}')
            for ecu in ecus:
                r_ecu = EcuTable.query.filter_by(Project_id=pid, abbr=ecu.strip()).first()
                # print(f'    EcuTable.query -> {r_ecu}')
                if r_ecu:
                    feature_ids = EcuMapFeatureTable.query.filter_by(project_id=pid, ecu_id=r_ecu.id).all()
                    for feature_id in feature_ids:
                        ecu_feature = EcuFeatureTable.query.get(feature_id.feature_id)
                        # print(f'    ECU: {r_ecu.abbr} , ecu_feature = {ecu_feature}')
                        if ecu_feature:
                            asset_code = f'CS-ITEM{item.number}-{r_ecu.abbr}-{ecu_feature.feature}'  #
                            # feature_map_security_attr_id FeatureMapSecurityAttrTable
                            sas = FeatureMapSecurityAttrTable.query.filter_by(feature_id=ecu_feature.id).all()
                            i = 1
                            # print(f'    {ecu_feature.id}, {ecu_feature.feature}, SAs={str(sas)}')
                            for sa in sas:  # 遍历安全属性
                                tat = ThreatAnalysisTable.query.filter_by(ecu_id=r_ecu.id,
                                                                            ecu_feature_id=ecu_feature.id,
                                                                            security_attr_id=sa.security_attr_id,
                                                                            project_id=pid
                                                                          ).first()
                                if tat is None:
                                    print(f'  Error: not found  ThreatAnalysisTable: {r_ecu.id}, {ecu_feature.id}')
                                    break
                                threat_analysis_id = tat.id

                                threat_analysis_rule_process(tat, ecu_feature, sa)

                                risk_code = f'RISK-ITEM{item.number}-{r_ecu.abbr}-{ecu_feature.feature}-{i:02d}'
                                # print(f'risk code-> {risk_code}')
                                cache = get_risk_assessment_from_cache(item.name, r_ecu.name, r_ecu.abbr, ecu_feature.feature, sa.security_attr.name)
                                result = risk_assessment_rule_process(item, r_ecu, ecu_feature, sa)

                                cs_req = get_risk_assessment_req(ecu_feature.id,sa.security_attr_id, pid)
                                new_r = RiskAssessmentEcuTable(item_id=item.id, ecu_id=r_ecu.id,
                                           ecu_feature_id=ecu_feature.id,
                                           asset_code=asset_code,
                                           feature_map_security_attr_id=sa.security_attr_id,
                                           threat_analysis_id=threat_analysis_id,
                                           damage_scenario_desc=result.damage_scenario_desc,
                                           risk_code=risk_code,
                                           safe_affect_id=result.safe_affect_id,
                                           economy_affect_id=result.economy_affect_id,
                                           operator_affect_id=result.operator_affect_id,
                                           private_affect_id=result.private_affect_id,
                                           affect_value=result.affect_value,
                                           affect_level=result.affect_level,
                                           risk_level=result.risk_level,
                                           cybersecurity_goal_name=f'应保护{asset_code}的{sa.security_attr.name}',
                                           risk_treatment_strategy_id=cache.risk_treatment_strategy_id,
                                           cybersecurity_req_id=cs_req.id if cs_req else 0,
                                           r_attack_feasibility_level=cache.r_attack_feasibility_level,
                                           r_risk_est_r_level=cache.r_risk_est_r_level,
                                           r_risk_treatment_strategy_id=1, #风险接受
                                           project_id=pid
                                )
                                i += 1
                                db.session.add(new_r)
                                db.session.commit()
                                auto_calc_risk_level(new_r)
                                if new_r.risk_level and int(new_r.risk_level) < 3 :
                                    new_r.cybersecurity_req_id = 0

                                new_count += 1

    db.session.commit()
    return new_count

def threat_analysis_rule_process(threat_analysis_table, feature, sec_attr):
    desc = AssetScenarioDescTable.query.filter_by(ecu_feature_id=feature.id, security_attr_id=sec_attr.security_attr_id).first()
    if desc:
        threat_analysis_table.attack_path = desc.attack_path
        threat_analysis_table.thread_scenario = desc.thread_scenario
    else:
        print(f'-- Error: AssetScenarioDescTable query fail.  threat_analysis_rule_process({threat_analysis_table}, {feature}, {sec_attr}) ')



def risk_assessment_rule_process(item, ecu, feature, sec_attr):
    tara = RiskAssessmentEcuTable()

    desc = AssetScenarioDescTable.query.filter_by(ecu_feature_id=feature.id, security_attr_id=sec_attr.security_attr_id).first()
    if desc:
        if desc.damage_scenario_desc:
            tara.damage_scenario_desc = (desc.damage_scenario_desc
                                         .replace("{{资产}}", feature.feature)
                                         .replace("{{安全属性}}", sec_attr.security_attr.name)
                                         .replace("{{ECU}}", ecu.abbr).replace("{{Item}}", item.name))
        else:
            tara.damage_scenario_desc = '-'

    if item.safety_impact_level is None or item.economic_impact_level is None or item.operation_impact_level is None or item.privacy_impact_level is None:
        print(f'-- Error: item.impact_level is None. {item.name}')
        return tara

    impact_level_s = str(get_impact_level(item.safety_impact_level, ecu.affect_level))
    impact_level_f = str(get_impact_level(item.economic_impact_level, ecu.affect_level))
    impact_level_o = str(get_impact_level(item.operation_impact_level, ecu.affect_level))
    impact_level_p = str(get_impact_level(item.privacy_impact_level, ecu.affect_level))

    out_levels = AssetRiskScenarioAssessmentTable.query.filter_by(ecu_feature_id=feature.id, security_attr_id=sec_attr.security_attr_id, type='sfop').all()
    for out_level in out_levels:
        levels = out_level.out_level.split(',')
        # print(f'levels: {levels}')
        if impact_level_s in out_level.level:
            impact_level_s = levels[0]
        if impact_level_f in out_level.level:
            impact_level_f = levels[1]
        if impact_level_o in out_level.level:
            impact_level_o = levels[2]
        if impact_level_p in out_level.level:
            impact_level_p = levels[3]
    # print(f'- 影响等级 - {impact_level_s}, {impact_level_f}, {impact_level_o}, {impact_level_p} -')
    para_s = RiskAssessmentParaTable.query.filter_by(type='安全影响', name=impact_level_s).first()
    para_f = RiskAssessmentParaTable.query.filter_by(type='经济影响', name=impact_level_f).first()
    para_o = RiskAssessmentParaTable.query.filter_by(type='操作影响', name=impact_level_o).first()
    para_p = RiskAssessmentParaTable.query.filter_by(type='隐私影响', name=impact_level_p).first()

    if para_s and para_f and para_o and para_p:
        tara.safe_affect_id = para_s.id
        tara.economy_affect_id = para_f.id
        tara.operator_affect_id = para_o.id
        tara.private_affect_id = para_p.id
    else:
        print(
            f'-- Error: RiskAssessmentParaTable not found. {impact_level_s}, {impact_level_f}, {impact_level_o}, {impact_level_p}')
        print(f'out_levels={out_levels}, feature.id={feature.id}, security_attr_id={sec_attr.security_attr_id}')
        # return None
    return tara
# 生成 code_or 字段
def generate_code_or(name_or):
    # 查询当前 name_or 的最大编号
    max_code_or = db.session.query(func.max(CybersecurityReqTable.code_or)).filter(CybersecurityReqTable.name_or == name_or).scalar()
    if max_code_or:
        # 如果存在，则提取编号并加1
        number = int(max_code_or.split('-')[1]) + 1
    else:
        # 如果不存在，则从1开始
        number = 1
    return f'IR-{number:03d}'

# 生成 code_dr 字段
def generate_code_dr(code_or, name_dr):
    # 查询当前 code_or 和 name_dr 的最大编号
    max_code_dr = db.session.query(func.max(CybersecurityReqTable.code_dr)).filter(CybersecurityReqTable.code_or == code_or, CybersecurityReqTable.name_dr == name_dr).scalar()
    if max_code_dr:
        # 如果存在，则提取编号并加1
        number = int(max_code_dr.split('-')[2]) + 1
    else:
        # 如果不存在，则从1开始
        number = 1
    return f'{code_or}-{number:02d}'

# 导入 安全需求库 CybersecurityReqTable
def import_cybersecurity_req_table(pid, file_path, sheet_name):
    CybersecurityReqTable.query.filter_by(project_id=0).delete(synchronize_session=False)
    db.session.commit()
    # print(f'import_cybersecurity_req_table:表项已清空. file_path:{file_path}, sheet_name:{sheet_name}')
    count = 0
    try:
        # 读取Excel文件
        df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=1)
        print(f'>import_cybersecurity_req_table: {file_path}')

        for index, row in df.iterrows():
            name_dr = str(row['设计需求标题']).strip()
            desc_dr = row['安全需求描述']
            code_dr = row['设计需求编号']
            name_or = str(row['初始需求标题']).strip()
            desc_or = row['初始需求描述']
            code_or = row['初始需求编号']
            feature_req = str(row['资产']).strip()
            type_req = str(row['类别']).strip()
            ca_level = str(row['推荐实现']).strip()
            # print(f'    {index}:  {code_or}, {code_dr}, {name_dr}')

            if name_dr and name_dr != '':
                # cs_req = CybersecurityReqTable.query.filter_by(name_dr=name_dr, project_id=0).first()
                # # print(f'    Query result: {cs_req}')
                # if cs_req:  # 覆盖
                #     cs_req.desc_dr = desc_dr
                #     cs_req.desc_or = desc_or
                #     cs_req.feature = feature_req
                #     cs_req.type = type_req
                #     cs_req.name_or = name_or
                #     cs_req.code_or = code_or #generate_code_or(name_or)
                #     cs_req.code_dr = code_dr #generate_code_dr(cs_req.code_or,name_dr)
                #     cs_req.cal1 = row['CAL1']
                #     cs_req.cal2 =row['CAL2']
                #     cs_req.cal3 =row['CAL3']
                #     cs_req.cal4 =row['CAL4']
                #     cs_req.r155 =row['R155']
                #     cs_req.r156 =row['R156']
                #     cs_req.gb44495 =row['GB 44495']
                #     cs_req.gb44496 =row['GB 44496']
                #     cs_req.gb_new_req =row['国标新增需求']
                #     cs_req.other =row['其他来源']
                #     cs_req.remark =row['备注']
                #     print(f'    重复: {index}:  {code_or}, {code_dr}, {name_dr}')
                #
                # else:  # 新建
                    # code_or = generate_code_or(name_or)
                r = CybersecurityReqTable(
                    name_dr=name_dr,
                    desc_dr=desc_dr,
                    desc_or=desc_or,
                    feature=feature_req,
                    type=type_req,
                    name_or=name_or,
                    code_or=code_or,
                    code_dr=code_dr,
                    ca_level=ca_level,
                    cal1 = row['CAL1'],
                    cal2 = row['CAL2'],
                    cal3 = row['CAL3'],
                    cal4 = row['CAL4'],
                    r155 = row['R155'],
                    r156 = row['R156'],
                    gb44495 = row['GB 44495'],
                    gb44496 = row['GB 44496'],
                    gb_new_req = row['国标新增需求'],
                    other = row['其他来源'],
                    remark = row['备注'],
                    project_id = 0
                )
                db.session.add(r)
                count += 1
                # print(f'    new req: {r}')

        # order_cybersecurity_req_table(pid)

    except ValueError as e:
        print(f"Error: {e}")
        # print(f"Value type: {type(value)}")
        # print(f"Value: {value}")
    db.session.commit()
    print(f' return {count}')
    return count

def import_tara_table(pid, file_path, sheet_name):
    print(backup_database())
    count = 0
    try:
        # 读取Excel文件
        df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=2)
        print(f'>import_tara_table: {file_path}')
        position_i = df.columns.get_loc('危害场景描述') # I
        position_ab = df.columns.get_loc('攻击路径描述')  # AB
        for index, row in df.iterrows():
                thread_scenario_number = str(row['威胁编号']).strip()
                thread_scenario = row['威胁场景']
                attack_path = row['攻击路径描述']

                level_1 = row.iloc[position_ab + 3]
                level_2 = row.iloc[position_ab + 6]
                level_3 = row.iloc[position_ab + 9]
                level_4 = row.iloc[position_ab + 12]
                level_5 = row.iloc[position_ab + 15]
                # print(f'威胁编号={thread_scenario_number}, {thread_scenario},{attack_path}, {level_1, level_2, level_3, level_4, level_5}')
                changed = False
                threat_table = ThreatAnalysisTable.query.filter_by(project_id=pid, thread_scenario_number=thread_scenario_number).first()
                if threat_table is None:
                    print(f"=== Error: not founded {thread_scenario_number}")
                else:
                    if threat_table.thread_scenario != thread_scenario:
                        threat_table.thread_scenario = thread_scenario
                        # print(f'threat_table.thread_scenario = {thread_scenario}')
                        count += 1
                    if threat_table.attack_path != attack_path:
                        threat_table.attack_path = attack_path
                        # print(f'threat_table.attack_path = {attack_path}')
                        count += 1

                    para1 = ThreadAssessmentParaTable.query.filter_by(type='发现漏洞所需要的时间', name=level_1).first()
                    if para1 and threat_table.vdt_id != para1.id:
                        changed = True
                        threat_table.vdt_id = para1.id
                        update_cache_threat_analysis(threat_table, 'vdt_id', para1.id)

                    para2 = ThreadAssessmentParaTable.query.filter_by(type='攻击者专业水平', name=level_2).first()
                    if para2 and threat_table.asl_id != para2.id:
                        changed = True
                        threat_table.asl_id = para2.id
                        update_cache_threat_analysis(threat_table, 'asl_id', para2.id)

                    para3 = ThreadAssessmentParaTable.query.filter_by(type='对目标的知识度水平', name=level_3).first()
                    if para3 and threat_table.knowledge_level_id != para3.id:
                        changed = True
                        threat_table.knowledge_level_id = para3.id
                        update_cache_threat_analysis(threat_table, 'knowledge_level_id', para3.id)

                    para4 = ThreadAssessmentParaTable.query.filter_by(type='威胁被利用的机会窗口水平', name=level_4).first()
                    if para4 and threat_table.woo_id != para4.id:
                        changed = True
                        threat_table.woo_id = para4.id
                        update_cache_threat_analysis(threat_table, 'woo_id', para4.id)

                    para5 = ThreadAssessmentParaTable.query.filter_by(type='攻击所需设备水平', name=level_5).first()
                    if para5 and threat_table.device_id != para5.id:
                        changed = True
                        threat_table.device_id = para5.id
                        update_cache_threat_analysis(threat_table, 'device_id', para5.id)
                    if para1 is None or para2 is None or para3 is None or para4 is None or para5 is None:
                        print(f"===== Error: not founded {thread_scenario_number}, {level_1}, {level_2}, {level_3}, {level_4}, {level_5}")

                if changed:
                    auto_calc_attack_level(threat_table)

                # -------------------------------------------------------------------------
                damage_code = str(row['危害场景编号']).strip()
                damage_desc = row['危害场景描述']
                affect_s = row.iloc[position_i + 2]
                affect_f = row.iloc[position_i + 5]
                affect_o = row.iloc[position_i + 8]
                affect_p = row.iloc[position_i + 11]
                risk_table = RiskAssessmentEcuTable.query.filter_by(project_id=pid, risk_code=damage_code).first()
                if risk_table:
                    if risk_table.damage_scenario_desc != damage_desc:
                        risk_table.damage_scenario_desc = damage_desc
                        count += 1
                        update_cache_risk_assessment(risk_table, 'damage_scenario_desc', damage_desc)

                    para = RiskAssessmentParaTable.query.filter_by(type='安全影响', name=affect_s).first()
                    if para and risk_table.safe_affect_id != para.id:
                        risk_table.safe_affect_id = para.id
                        count += 1
                        update_cache_risk_assessment(risk_table, 'safe_affect_id', para.id)
                        changed = True
                    para = RiskAssessmentParaTable.query.filter_by(type='经济影响', name=affect_f).first()
                    if para and risk_table.economy_affect_id != para.id:
                        risk_table.economy_affect_id = para.id
                        count += 1
                        update_cache_risk_assessment(risk_table, 'economy_affect_id', para.id)
                        changed = True
                    para = RiskAssessmentParaTable.query.filter_by(type='操作影响', name=affect_o).first()
                    if para and risk_table.operator_affect_id != para.id:
                        risk_table.operator_affect_id = para.id
                        count += 1
                        update_cache_risk_assessment(risk_table, 'operator_affect_id', para.id)
                        changed = True
                    para = RiskAssessmentParaTable.query.filter_by(type='隐私影响', name=affect_p).first()
                    if para and risk_table.private_affect_id != para.id:
                        risk_table.private_affect_id = para.id
                        count += 1
                        update_cache_risk_assessment(risk_table, 'private_affect_id', para.id)
                        changed = True
                if changed:
                    auto_calc_risk_level(risk_table)

    except ValueError as e:
        print(f"Error: {e}")

    db.session.commit()
    print(f' return {count}')
    return count

def order_cybersecurity_req_table(pid):
    # 读取所有表项并按 name_or 排序
    records = CybersecurityReqTable.query.filter_by(project_id=pid).order_by(CybersecurityReqTable.name_or).all()

    # 初始化计数器
    or_code = 0
    dr_code = 1
    previous_name_or = None

    for record in records:
        if record.name_or != previous_name_or:
            # 如果 name_or 与上一条记录不同，则 or_code++，dr_code 置1
            or_code += 1
            dr_code = 1
        else:
            dr_code += 1

        record.code_or = f'IR-{or_code:03d}'
        record.code_dr = f'DR-{or_code:03d}-{dr_code:02d}'

        previous_name_or = record.name_or

    db.session.commit()

def auto_calc_attack_level(threat_analysis):
    if threat_analysis:
        # print(f'{threat_analysis.id} >>> ID : {threat_analysis.vdt_id}, {threat_analysis.asl_id}, {threat_analysis.knowledge_level_id}, {threat_analysis.woo_id}, {threat_analysis.device_id}')
        if threat_analysis.vdt is None or threat_analysis.asl is None or threat_analysis.knowledge_level is None \
            or threat_analysis.woo is None or threat_analysis.device is None:
            print(f'auto_calc_attack_level ERROR: {threat_analysis.vdt}, {threat_analysis.asl}, {threat_analysis.knowledge_level}, {threat_analysis.woo}, {threat_analysis.device}')
            return False

        attack_value = (threat_analysis.vdt.value + threat_analysis.asl.value
                                        + threat_analysis.knowledge_level.value
                                        + threat_analysis.woo.value
                                        + threat_analysis.device.value)
        # print(f'== attack_value={attack_value}')
        if attack_value <= 13:
            threat_analysis.attack_level = '高'
        elif attack_value <= 19:
            threat_analysis.attack_level = '中'
        elif attack_value <= 24:
            threat_analysis.attack_level = '低'
        else:
            threat_analysis.attack_level = '非常低'

        threat_analysis.attack_value = attack_value
        # print(f'attack_value = {attack_value}, level= {threat_analysis.attack_level}')
        db.session.commit()
    # return ''
def auto_calc_attack_level_all():
    results = ThreatAnalysisTable.query.all()
    for result in results:
        auto_calc_attack_level(result)

def auto_calc_risk_level_all():
    results = RiskAssessmentEcuTable.query.all()
    for result in results:
        auto_calc_risk_level(result)

def auto_calc_risk_level(risk_assessment):
    if risk_assessment:
        # risk_assessment = db.session.get(RiskAssessmentEcuTable, risk_id)
        if risk_assessment.safe_affect and risk_assessment.economy_affect and risk_assessment.operator_affect and risk_assessment.private_affect:
            total = (risk_assessment.safe_affect.value
                     + risk_assessment.economy_affect.value
                     + risk_assessment.operator_affect.value
                     + risk_assessment.private_affect.value)

            risk_assessment.affect_value = total
            # print(f' --- affect value: {risk_assessment.affect_value} ({risk_assessment.safe_affect.value},{risk_assessment.economy_affect.value},{risk_assessment.operator_affect.value})')
            if total >= 1000:
                risk_assessment.affect_level = '严重的'
            elif total >= 100:
                risk_assessment.affect_level = '重大的'
            elif total >= 10:
                risk_assessment.affect_level = '中等的'
            else:
                risk_assessment.affect_level = '无明显影响的'

            risk_assessment.risk_level = get_risk_level(risk_assessment.threat_analysis.attack_level,
                                                        risk_assessment.affect_level)
            if int(risk_assessment.risk_level) <= 2:
                risk_assessment.risk_treatment_strategy_id = 1 # 风险接受
                risk_assessment.r_risk_est_r_level = risk_assessment.risk_level
            else:
                risk_assessment.risk_treatment_strategy_id = 4 # 风险降低
                risk_assessment.r_risk_est_r_level = '2'

        else:
            print(f'-- Error: auto_calc_risk_level input Null')
        # db.session.commit()

def get_risk_level_desc(level):
    # 定义风险级别映射
    risk_levels = {
        '1': '极低',
        '2': '低',
        '3': '中',
        '4': '高',
        '5': '极高'
    }

    # 检查输入是否有效
    if level not in risk_levels:
        level = '1'
        # raise ValueError("无效的输入，必须是 '1' 到 '5' 之间的字符串。")

    # 返回对应的风险描述
    return risk_levels[level]
# 残余风险计算
def auto_calc_r_risk_level(risk_assessment):
    risk_assessment.r_risk_est_r_level = get_risk_level(risk_assessment.r_attack_feasibility_level,
                                                        risk_assessment.affect_level)
    update_cache_risk_assessment(risk_assessment, 'r_risk_est_r_level', risk_assessment.r_risk_est_r_level)

    if int(risk_assessment.r_risk_est_r_level) <= 2:
        risk_assessment.r_risk_treatment_strategy_id = 1  # 风险接受
        update_cache_risk_assessment(risk_assessment, 'r_risk_treatment_strategy_id', 1)

    db.session.commit()

def duplicate_req_to_project(pid):
    """
        将项目ID为0的需求复制到指定项目ID中
        :param pid: 目标项目ID
        :return: 复制的需求数量
        """
    count = 0
    CybersecurityReqTable.query.filter_by(project_id=pid).delete()
    db.session.commit()
    reqs_base = CybersecurityReqTable.query.filter_by(project_id=0).all()
    new_reqs = []

    for req_base in reqs_base:
        # 检查是否已存在同名需求
        # req = CybersecurityReqTable.query.filter_by(code_or=req_base.code_or, name_dr=req_base.name_dr, project_id=pid).first()
        # if req is None:

        # 创建新需求
        req = CybersecurityReqTable(project_id=pid)
        new_reqs.append(req)
        count += 1

        # 复制字段
        copy_cybersecurity_req_fields(req_base, req)

    try:
        # 批量插入新需求
        if new_reqs:
            db.session.bulk_save_objects(new_reqs)
        db.session.commit()
    except SQLAlchemyError as e:
        db.session.rollback()
        print(f'Failed to duplicate requirements: {e}')
        return 0

    return count
def copy_cybersecurity_req_fields(src, dest):
    """
    复制 CybersecurityReqTable 的字段（排除 id 和 project_id）
    :param src: 源对象
    :param dest: 目标对象
    """
    exclude_fields = {'id', 'project_id', '_sa_instance_state'}
    for key, value in src.__dict__.items():
        if key not in exclude_fields:
            setattr(dest, key, value)


# 假设这是你的数据库连接和查询函数
def get_tara_data_from_db(pid):
    # 查询 RiskAssessmentEcuTable 表中的所有数据，并加载关联表的数据
    query = db.session.query(RiskAssessmentEcuTable).options(
        joinedload(RiskAssessmentEcuTable.item),
        joinedload(RiskAssessmentEcuTable.ecu),
        joinedload(RiskAssessmentEcuTable.ecu_feature),
        joinedload(RiskAssessmentEcuTable.security_attr),
        joinedload(RiskAssessmentEcuTable.threat_analysis),

        joinedload(RiskAssessmentEcuTable.safe_affect),
        joinedload(RiskAssessmentEcuTable.economy_affect),
        joinedload(RiskAssessmentEcuTable.operator_affect),
        joinedload(RiskAssessmentEcuTable.private_affect),

        joinedload(RiskAssessmentEcuTable.risk_treatment_strategy),
        joinedload(RiskAssessmentEcuTable.cybersecurity_req)
    ).filter_by(project_id=pid).all()

    # 将查询结果转换为字典列表
    data = []
    for row in query:
        data.append({
            'id': row.id,
            'Item': row.item.name if row.item else None,
            'ECU': row.ecu.abbr if row.ecu else None,
            '特性': row.ecu_feature.feature if row.ecu_feature else None,
            '资产编码': row.asset_code,
            '安全属性': row.security_attr.name if row.security_attr else None,
            '威胁场景': row.threat_analysis.thread_scenario if row.threat_analysis else None,
            '损害场景': row.damage_scenario_desc,
            '风险编号': row.risk_code,
            '安全影响': row.safe_affect.name,
            '经济影响': row.economy_affect.name,
            '操作影响': row.operator_affect.name,
            '隐私影响': row.private_affect.name,
            '影响等级': row.affect_level,
            '风险等级': row.risk_level,
            '风险处置策略': row.risk_treatment_strategy.name,

            '安全需求': row.cybersecurity_req.name_or if row.cybersecurity_req else None

        })

    # 使用 pandas 创建 DataFrame
    df = pd.DataFrame(data)

    return df
def replace_placeholders(text, ecu_name, ecu_abbr):
    if text:
        text = re.sub(r'\{\{ EcuTable\.name }}', ecu_name, text)
        text = re.sub(r'\{\{ EcuTable\.abbr }}', ecu_abbr, text)  # 假设abbr与name相同
    return text

def copy_row_format(sheet, source_row, target_row):
    for col in range(1, sheet.max_column + 1):
        source_cell = sheet.cell(row=source_row, column=col)
        target_cell = sheet.cell(row=target_row, column=col)
        target_cell.font = source_cell.font
        target_cell.border = source_cell.border
        target_cell.fill = source_cell.fill
        # target_cell.number_format = source_cell.number_format
        # target_cell.protection = source_cell.protection
        # target_cell.alignment = source_cell.alignment

def is_match_cal_req(req_record, ecu_record):
    # 定义 feature 到 EcuTable 字段的映射
    feature_field_mapping = {
        "PCB": "pcb",
        "芯片": "chipset",
        "Bootloader": "Bootloader",
        "第三方应用": "app_3rd",
        "通用操作系统": "OS",

        "OEM原生应用": "app",
        "重要数据": "data_1",
        "个人数据": "data_2",
        "关键数据": "data_3",
        "密钥": "data_key",
        "车辆识别数据": "data_car_info",
        "日志": "data_log",
        "数据外发": "data_export",
        "数据跨境": "data_cross_border",
        "USB接口": "interface_usb",
        "SD卡接口": "interface_sd",
        "调试接口": "interface_debug",
        "CAN通信": "can_bus",
        "以太通信": "eth_bus",
        "FlexRay通信": "flex_ray_bus",
        "LIN通信": "lin_bus",
        "蓝牙": "wireless_bt",
        "WIFI": "wireless_wifi",
        "射频通信": "wireless_rf",
        "V2X": "wireless_v2x",
        "GNSS": "wireless_gnss",
        "蜂窝通信": "wireless_mobile",
        "OTA": "ota_upgrade",
        "USB升级": "usb_upgrade",
        "OBD升级": "obd_upgrade",
        "OBD诊断": "obd_debug",
        "远程诊断": "remote_debug",
        "其他远程控制指令": "other_remote_cmd",
        "IDPS": "intrusion_detection_prevention_systems",
        "车载升级主控节点": "upgrade_node",
        "TBox": "tbox",
        "网关": "gate_way",
    }
    if req_record.feature:
        try:
            features = [word.strip() for word in re.split(r'[;,&]', req_record.feature)]
            # print(features)
            for feature in features:
                # 检查 feature 是否在映射中
                if feature in feature_field_mapping:
                    # 获取 ecu_record 的字段值
                    field_value = getattr(ecu_record, feature_field_mapping[feature], None)
                    if field_value and '√' in field_value:
                        return True
                    if feature == "通用操作系统" and len(field_value.strip()) > 2:
                        return True

        except AttributeError as e:
            print(f'AttributeError: {e}')
            return False
    return False

def export_tara_report_to_word(target_path, pid):
    """
    将TARA分析结果导出到Word文档
    :param target_path: 目标Word文件路径
    :param pid: 项目ID
    """
    try:
        # 获取项目信息
        project = ProjectTable.query.get(pid)
        if not project:
            print(f'Project {pid} not found')
            raise ValueError("项目不存在")
        items = ItemTable.query.filter_by(project_id=pid).all()
        funcs = FuncRelationTable.query.filter_by(project_id=pid).all()
        tara= RiskAssessmentEcuTable.query.filter_by(project_id=pid).all()

        thread_count_all = ThreatAnalysisTable.query.filter_by(project_id=pid).count()
        thread_count_1 = ThreatAnalysisTable.query.filter_by(project_id=pid, attack_level='高').count()
        thread_count_2 = ThreatAnalysisTable.query.filter_by(project_id=pid, attack_level='中').count()
        thread_count_3 = ThreatAnalysisTable.query.filter_by(project_id=pid, attack_level='低').count()
        thread_count_4 = ThreatAnalysisTable.query.filter_by(project_id=pid, attack_level='非常低').count()

        tara_count_all = RiskAssessmentEcuTable.query.filter_by(project_id=pid).count()
        tara_count_1 = RiskAssessmentEcuTable.query.filter_by(project_id=pid, affect_level='严重的').count()
        tara_count_2 = RiskAssessmentEcuTable.query.filter_by(project_id=pid, affect_level='重大的').count()
        tara_count_3 = RiskAssessmentEcuTable.query.filter_by(project_id=pid, affect_level='中等的').count()
        tara_count_4 = RiskAssessmentEcuTable.query.filter_by(project_id=pid, affect_level='无明显影响的').count()
        risk_level_count_4 = RiskAssessmentEcuTable.query.filter_by(project_id=pid, risk_level='4').count()
        risk_level_count_3 = RiskAssessmentEcuTable.query.filter_by(project_id=pid, risk_level='3').count()
        risk_level_count_2 = RiskAssessmentEcuTable.query.filter_by(project_id=pid, risk_level='2').count()
        risk_level_count_1 = RiskAssessmentEcuTable.query.filter_by(project_id=pid, risk_level='1').count()

        # 获取TARA数据
        # tara_data = get_tara_data_from_db(pid)
        
        # 使用python-docx处理Word文档
        doc = Document(target_path)
        # style_title = doc.styles.add_style('CustomStyle', 1)  # 1 表示段落样式
        # style_title.font.name = '新宋体'
        # style_title.font.size = Pt(22)
        # 1. 更新文档占位符
        for paragraph in doc.paragraphs:
            if '{{车型}}' in paragraph.text:
                # 遍历段落中的所有 runs
                for run in paragraph.runs:
                    if '{{车型}}' in run.text:
                        # 替换文本，并保留原有格式
                        run.text = run.text.replace('{{车型}}', project.abbr)
                # paragraph.text = paragraph.text.replace('{{车型}}', project.abbr)
                # paragraph.style = style_title
                # for run in paragraph.runs:
                #     # run.font.name = font_name
                #     # run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)  # 设置中文字体
                #     run.font.size = Pt(22)
            if '{{item.count}}' in paragraph.text:
                paragraph.text = paragraph.text.replace('{{item.count}}', str(len(items)))
            if '{{func.count}}' in paragraph.text:
                paragraph.text = paragraph.text.replace('{{func.count}}', str(len(funcs)))
            if '{tara.count}' in paragraph.text:
                paragraph.text = paragraph.text.replace('{tara.count}', str(tara_count_all))
            if '{thread.count}' in paragraph.text:
                paragraph.text = paragraph.text.replace('{thread.count}', str(thread_count_all))
        print(f'更新文档占位符 OK')

        # 2. 插入 Item 列表及数据流图
        position = None
        for i, paragraph in enumerate(doc.paragraphs):
            if '{item信息}' in paragraph.text:
                position = i
                print(f'找到 item插入位置: {position}')
                paragraph.clear()  # 清除段落内容
                break

        if position is not None:
            # 获取占位符段落
            placeholder = doc.paragraphs[position]

            # 为每个Item添加内容
            for item in items:
                # 添加3级标题
                new_heading = placeholder.insert_paragraph_before(f'{item.number} {item.name}', style='Heading 3')

                # 添加功能描述
                desc_para = placeholder.insert_paragraph_before()
                desc_para.add_run('功能描述: ').bold = True
                desc_para.add_run(item.desc if item.desc else '无')

                # 添加数据流图（如果存在）
                desc_para = placeholder.insert_paragraph_before()
                desc_para.add_run('数据流图: ').bold = True
                uml_path = f'static/plantuml/{pid}/item_{item.id}_uml.png'
                if os.path.exists(uml_path):
                    # 创建一个新段落用于插入图片
                    pic_para = placeholder.insert_paragraph_before()
                    run = pic_para.add_run()

                    # 获取缩放后的宽度
                    scaled_width = get_scaled_width(uml_path, 6)
                    if scaled_width:
                        # print(f'scaled_width={scaled_width}')
                        run.add_picture(uml_path, width=scaled_width)  # 缩放后插入

                    # 添加图片说明
                    caption = placeholder.insert_paragraph_before(f'图{item.number} {item.name}数据流图', style='Caption')
                    caption.alignment = WD_ALIGN_PARAGRAPH.CENTER
                    # 设置段落居中对齐
                    pic_para.alignment = WD_ALIGN_PARAGRAPH.CENTER

                else:
                    desc_para.add_run('无')
        print(f'Item OK')
        # # 遍历文档中的所有段落
        # for paragraph in doc.paragraphs:
        #     if '{item信息}' in paragraph.text:
        #         paragraph.clear()  # 清除段落内容
        #         break  # 找到后退出循环
        # 遍历文档中的所有表格
        for table in doc.tables:
            # 检查表格的行数和列数是否足够
            if len(table.rows) > 4 and len(table.columns) ==3:

                if table.cell(0,1).text.strip() == '损害场景级别':
                    table.cell(1, 2).text = str(tara_count_1) # 严重
                    table.cell(2, 2).text = str(tara_count_2)
                    table.cell(3, 2).text = str(tara_count_3)
                    table.cell(4, 2).text = str(tara_count_4)

                if table.cell(0,1).text.strip() == '威胁场景级别':
                    table.cell(1, 2).text = str(thread_count_1) # 严重
                    table.cell(2, 2).text = str(thread_count_2)
                    table.cell(3, 2).text = str(thread_count_3)
                    table.cell(4, 2).text = str(thread_count_4)

                if table.cell(0,1).text.strip() == '风险等级':
                    table.cell(1, 2).text = str(risk_level_count_4)
                    table.cell(2, 2).text = str(risk_level_count_3)
                    table.cell(3, 2).text = str(risk_level_count_2)
                    table.cell(4, 2).text = str(risk_level_count_1)

                set_table_center(table)

            if table.cell(0, 2).text.strip() == '安全需求数量':
                print(f'安全需求数量 OK')
                ecus = EcuTable.query.filter_by(Project_id=pid).all()
                number = 0
                print(f'ECU table: {len(ecus)}, target_path={target_path}')

                for ecu in ecus:
                    number += 1
                    table.cell(number, 0).text = str(number)
                    # table.cell(number, 0).paragraphs.alignment = WD_ALIGN_PARAGRAPH.CENTER
                    table.cell(number, 1).text = ecu.abbr_ext if ecu.abbr_ext else ecu.abbr
                    table.cell(number, 2).text = ecu.remark if ecu.remark else ''
                    table.cell(number, 3).text = ecu.vendor if ecu.vendor else ''

                    table.add_row()

                set_table_center(table)
                set_table_borders(table)
            set_table_cell_paragraph_spacing(table)
        # 保存修改后的文档
        doc.save(target_path)
        print(f'--doc.save({target_path})')
        return True
        
    except Exception as e:
        print(f'导出TARA报告Word失败: {str(e)}')
        return False
def set_table_center(table):
    table.style = None
    for row in table.rows:
        # 遍历行中的每个单元格
        for cell in row.cells:
            # 设置单元格内容水平居中
            for paragraph in cell.paragraphs:
                paragraph.alignment = WD_ALIGN_PARAGRAPH.CENTER

            # 设置单元格内容垂直居中
            cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER

def set_table_cell_paragraph_spacing(table, space_after=Pt(0)):
    """
    设置表格中所有单元格的段后间距

    :param table: 表格对象
    :param space_after: 段后间距，默认为 0 磅
    """
    # 遍历表格的所有行和列
    for row in table.rows:
        for cell in row.cells:
            # 遍历单元格中的所有段落
            for paragraph in cell.paragraphs:
                paragraph.paragraph_format.space_after = space_after


def set_table_borders(table, color = '000000'):
    """
    设置表格的所有边框为可见
    """
    # 获取表格的 XML 元素
    tbl = table._tbl

    # 创建边框属性
    tblBorders = OxmlElement('w:tblBorders')

    # 定义边框类型和宽度
    border_types = ['top', 'left', 'bottom', 'right', 'insideH', 'insideV']
    for border_type in border_types:
        border = OxmlElement(f'w:{border_type}')
        border.set(qn('w:val'), 'single')  # 边框类型为单线
        border.set(qn('w:sz'), '4')        # 边框宽度（单位为 1/8 磅）
        border.set(qn('w:space'), '0')     # 边框与内容的间距
        border.set(qn('w:color'), color)  # 边框颜色（黑色）
        tblBorders.append(border)

    # 将边框属性添加到表格中
    tblPr = tbl.xpath('.//w:tblPr')[0]
    tblPr.append(tblBorders)
def export_tara_table_to_excel(template_file, sheet_name, pid):
    print(f'export_tara_table_to_excel: {template_file}, {sheet_name}, {pid}')
    # 加载模板文件
    sheet_name = 'TARA评估表'
    try:
        wb = load_workbook(template_file)
        sheet = wb[sheet_name]
    except Exception as e:
        print(f'Failed to load tara template file: {e}')
        return False  #
        # 设置边框
    thin_border = Border(left=Side(style='thin'),
                         right=Side(style='thin'),
                         top=Side(style='thin'),
                         bottom=Side(style='thin'))
    row_index = 4
    sheet.delete_rows(4,3)
    sheet.column_dimensions['G'].hidden = True
    task = ProjectTable.query.get(pid)
    cg_counter = 0
    records = RiskAssessmentEcuTable.query.filter_by(project_id=pid).all()
    for record in records:
        # cg_counter += 1
        # if record.cybersecurity_req:
        sheet.row_dimensions[row_index].height = 33
        # reqs = CybersecurityReqTable.query.filter_by(name_or=record.cybersecurity_req.name_or).all()
        # for req in reqs:
        # print(f'有安全需求:{row_index}: {req.name_or}->{req.code_dr} {req.name_dr}')
        try:
            sheet.cell(row=row_index, column=1, value=f'ITEM-{task.abbr}-{record.item.number}' if record.item else '-')
            sheet.cell(row=row_index, column=2, value=record.item.name if record.item else '-')
            sheet.cell(row=row_index, column=3, value=record.ecu.name if record.ecu else '-')
            sheet.cell(row=row_index, column=4, value=record.ecu_feature.feature if record.ecu_feature else '-')
            # E: 资产编号
            sheet.cell(row=row_index, column=5, value=record.asset_code)
            sheet.cell(row=row_index, column=6, value=record.security_attr.name if record.security_attr else '-')
            sheet.cell(row=row_index, column=7, value='√') #安全属性关联分析
            sheet.cell(row=row_index, column=8, value=record.risk_code)
            sheet.cell(row=row_index, column=9, value=record.damage_scenario_desc)
            # J: 安全影响
            sheet.cell(row=row_index, column=10, value=record.safe_affect.value if record.safe_affect else '')
            sheet.cell(row=row_index, column=11, value=record.safe_affect.name if record.safe_affect else '')
            sheet.cell(row=row_index, column=12, value=record.safe_affect.explain if record.safe_affect else '')

            # M: 经济影响
            sheet.cell(row=row_index, column=13, value=record.economy_affect.value if record.economy_affect else '')
            sheet.cell(row=row_index, column=14, value=record.economy_affect.name if record.economy_affect else '')
            sheet.cell(row=row_index, column=15, value=record.economy_affect.explain if record.economy_affect else '')
            # P: 操作影响
            sheet.cell(row=row_index, column=16, value=record.operator_affect.value if record.operator_affect else '')
            sheet.cell(row=row_index, column=17, value=record.operator_affect.name if record.operator_affect else '')
            sheet.cell(row=row_index, column=18, value=record.operator_affect.explain if record.operator_affect else '')
            # S: 隐私影响
            sheet.cell(row=row_index, column=19, value=record.private_affect.value if record.private_affect else '')
            sheet.cell(row=row_index, column=20, value=record.private_affect.name if record.private_affect else '')
            sheet.cell(row=row_index, column=21, value=record.private_affect.explain if record.private_affect else '')
            # V: Total
            sheet.cell(row=row_index, column=22, value=record.affect_value)
            sheet.cell(row=row_index, column=23, value=record.affect_level)
            # X: 威胁场景
            if record.threat_analysis:
                sheet.cell(row=row_index, column=24, value=record.threat_analysis.thread_scenario_number)
                sheet.cell(row=row_index, column=25, value=record.threat_analysis.thread_scenario)
                sheet.cell(row=row_index, column=26, value=record.threat_analysis.thread_type.name)
                # AB: 攻击路径
                sheet.cell(row=row_index, column=27, value=record.threat_analysis.attack_path_number)
                sheet.cell(row=row_index, column=28, value=record.threat_analysis.attack_path)
                sheet.cell(row=row_index, column=29, value='N/A') # R155附录威胁编号

                # AD: 发现漏洞需要的时间
                sheet.cell(row=row_index, column=30, value=record.threat_analysis.vdt.value if record.threat_analysis.vdt else 'N/A')
                sheet.cell(row=row_index, column=31, value=record.threat_analysis.vdt.name if record.threat_analysis.vdt else 'N/A')
                sheet.cell(row=row_index, column=32, value=record.threat_analysis.vdt.type if record.threat_analysis.vdt else 'N/A')
                # AG: ASL
                sheet.cell(row=row_index, column=33, value=record.threat_analysis.asl.value if record.threat_analysis.asl else 'N/A')
                sheet.cell(row=row_index, column=34, value=record.threat_analysis.asl.name if record.threat_analysis.asl else 'N/A')
                sheet.cell(row=row_index, column=35, value=record.threat_analysis.asl.type if record.threat_analysis.asl else 'N/A')
                # AJ: 对目标的知识度水平
                sheet.cell(row=row_index, column=36, value=record.threat_analysis.knowledge_level.value if record.threat_analysis.knowledge_level else 'N/A')
                sheet.cell(row=row_index, column=37, value=record.threat_analysis.knowledge_level.name if record.threat_analysis.knowledge_level else 'N/A')
                sheet.cell(row=row_index, column=38, value=record.threat_analysis.knowledge_level.type if record.threat_analysis.knowledge_level else 'N/A')
                # AM: 威胁被利用的机会窗口
                sheet.cell(row=row_index, column=39, value=record.threat_analysis.woo.value if record.threat_analysis.woo else 'N/A')
                sheet.cell(row=row_index, column=40, value=record.threat_analysis.woo.name if record.threat_analysis.woo else 'N/A')
                sheet.cell(row=row_index, column=41, value=record.threat_analysis.woo.type if record.threat_analysis.woo else 'N/A')
                # AP: 攻击所需设备水平
                sheet.cell(row=row_index, column=42, value=record.threat_analysis.device.value if record.threat_analysis.device else 'N/A')
                sheet.cell(row=row_index, column=43, value=record.threat_analysis.device.name if record.threat_analysis.device else 'N/A')
                sheet.cell(row=row_index, column=44, value=record.threat_analysis.device.type if record.threat_analysis.device else 'N/A')

                sheet.cell(row=row_index, column=45, value=record.threat_analysis.attack_value)
                sheet.cell(row=row_index, column=46, value=record.threat_analysis.attack_level)
                sheet.cell(row=row_index, column=47, value='N/A') #
            else:
                print(f'Error record.threat_analysis is None: id={record.id}, pid={pid}')

            sheet.cell(row=row_index, column=48, value=get_risk_level_desc(record.risk_level))
            # AW: 风险处置对策
            sheet.cell(row=row_index, column=49, value=record.risk_treatment_strategy.name if record.risk_treatment_strategy else '')
            sheet.cell(row=row_index, column=50, value=record.ecu.cal_level if record.ecu else '-' ) # CAL级别
            sheet.cell(row=row_index, column=51, value=record.risk_treatment_strategy.desc) #
            sheet.cell(row=row_index, column=52, value='N/A') # 原因备注
            # BA: 网络安全目标
            if record.risk_level and record.risk_level in '345':
                cg_counter += 1
                sheet.cell(row=row_index, column=51, value='N/A')  #
                sheet.cell(row=row_index, column=53, value=f'CG-{task.abbr}-{cg_counter:04d}' )  # 编号
                sheet.cell(row=row_index, column=54, value=record.cybersecurity_goal_name)  #
                sheet.cell(row=row_index, column=55,
                           value=record.cybersecurity_req.code_or if record.cybersecurity_req else 'N/A')  #
                sheet.cell(row=row_index, column=56,
                           value=record.cybersecurity_req.name_or if record.cybersecurity_req else 'N/A')  #
            else:
                sheet.cell(row=row_index, column=53, value='N/A')  # 编号
                sheet.cell(row=row_index, column=54, value='N/A')  #
                sheet.cell(row=row_index, column=55, value='N/A')  #
                sheet.cell(row=row_index, column=56, value='N/A')  #

            sheet.cell(row=row_index, column=57, value=get_risk_level_desc(record.r_risk_est_r_level))  #
            sheet.cell(row=row_index, column=58, value=record.r_risk_treatment_strategy.name if record.r_risk_treatment_strategy else 'N/A')  #

            for col in range(1, 59):
                target_cell = sheet.cell(row=row_index, column=col)
                target_cell.border = thin_border
                target_cell.font = Font(name='微软雅黑')
                target_cell.alignment = Alignment(horizontal='center', vertical='center')
            row_index += 1
        except Exception as e:
            print(f'Error export_tara_table_to_excel:  {e}')
            return False

        # else: #'√'
        #     print(f'无安全需求:{row_index}: {record.item.name} {record.ecu.name}')
        #     sheet.cell(row=row_index, column=1, value=record.item.number if record.item else '-')
        #     sheet.cell(row=row_index, column=2, value=record.item.name if record.item else '-')
        #     sheet.cell(row=row_index, column=3, value=record.ecu.name if record.ecu else '-')
        #     sheet.cell(row=row_index, column=4, value=record.ecu_feature.feature if record.ecu_feature else '-')
        #     # E: 资产编号
        #     sheet.cell(row=row_index, column=5, value=record.asset_code)
        #     sheet.cell(row=row_index, column=6, value=record.security_attr.name if record.security_attr else '-')
        #     sheet.cell(row=row_index, column=7, value='√')  # 安全属性关联分析
        #     sheet.cell(row=row_index, column=8, value=record.risk_code)
        #     sheet.cell(row=row_index, column=9, value=record.damage_scenario_desc)
        #     # J: 安全影响
        #     sheet.cell(row=row_index, column=10, value=record.safe_affect.value if record.safe_affect else '')
        #     sheet.cell(row=row_index, column=11, value=record.safe_affect.name if record.safe_affect else '')
        #     sheet.cell(row=row_index, column=12, value=record.safe_affect.explain if record.safe_affect else '')
        #
        #     # M: 经济影响
        #     sheet.cell(row=row_index, column=13, value=record.economy_affect.value if record.economy_affect else '')
        #     sheet.cell(row=row_index, column=14, value=record.economy_affect.name if record.economy_affect else '')
        #     sheet.cell(row=row_index, column=15,
        #                value=record.economy_affect.explain if record.economy_affect else '')
        #     # P: 操作影响
        #     sheet.cell(row=row_index, column=16,
        #                value=record.operator_affect.value if record.operator_affect else '')
        #     sheet.cell(row=row_index, column=17,
        #                value=record.operator_affect.name if record.operator_affect else '')
        #     sheet.cell(row=row_index, column=18,
        #                value=record.operator_affect.explain if record.operator_affect else '')
        #     # S: 隐私影响
        #     sheet.cell(row=row_index, column=19, value=record.private_affect.value if record.private_affect else '')
        #     sheet.cell(row=row_index, column=20, value=record.private_affect.name if record.private_affect else '')
        #     sheet.cell(row=row_index, column=21,
        #                value=record.private_affect.explain if record.private_affect else '')
        #     # V: Total
        #     sheet.cell(row=row_index, column=22, value=record.affect_value)
        #     sheet.cell(row=row_index, column=23, value=record.affect_level)
        #     # X: 威胁场景
        #     sheet.cell(row=row_index, column=24, value=record.threat_analysis.thread_scenario_number)
        #     sheet.cell(row=row_index, column=25, value=record.threat_analysis.thread_scenario)
        #     sheet.cell(row=row_index, column=26, value=record.threat_analysis.thread_type.name)
        #     # AB: 攻击路径
        #     sheet.cell(row=row_index, column=27, value=record.threat_analysis.attack_path_number)
        #     sheet.cell(row=row_index, column=28, value=record.threat_analysis.attack_path)
        #     sheet.cell(row=row_index, column=29, value='R155附录威胁编号')  # R155附录威胁编号
        #     # AD: 发现漏洞需要的时间
        #     sheet.cell(row=row_index, column=30, value=record.threat_analysis.vdt.value)
        #     sheet.cell(row=row_index, column=31, value=record.threat_analysis.vdt.name)
        #     sheet.cell(row=row_index, column=32, value=record.threat_analysis.vdt.type)
        #     # AG: ASL
        #     sheet.cell(row=row_index, column=33, value=record.threat_analysis.asl.value)
        #     sheet.cell(row=row_index, column=34, value=record.threat_analysis.asl.name)
        #     sheet.cell(row=row_index, column=35, value=record.threat_analysis.asl.type)
        #     # AJ: 对目标的知识度水平
        #     sheet.cell(row=row_index, column=36, value=record.threat_analysis.knowledge_level.value)
        #     sheet.cell(row=row_index, column=37, value=record.threat_analysis.knowledge_level.name)
        #     sheet.cell(row=row_index, column=38, value=record.threat_analysis.knowledge_level.type)
        #     # AM: 威胁被利用的机会窗口
        #     sheet.cell(row=row_index, column=39, value=record.threat_analysis.woo.value)
        #     sheet.cell(row=row_index, column=40, value=record.threat_analysis.woo.name)
        #     sheet.cell(row=row_index, column=41, value=record.threat_analysis.woo.type)
        #     # AP: 攻击所需设备水平
        #     sheet.cell(row=row_index, column=42, value=record.threat_analysis.device.value)
        #     sheet.cell(row=row_index, column=43, value=record.threat_analysis.device.name)
        #     sheet.cell(row=row_index, column=44, value=record.threat_analysis.device.type)
        #
        #     sheet.cell(row=row_index, column=45, value=record.threat_analysis.attack_value)
        #     sheet.cell(row=row_index, column=46, value=record.threat_analysis.attack_level)
        #     sheet.cell(row=row_index, column=47, value='')  #
        #
        #     sheet.cell(row=row_index, column=48, value=record.risk_level)
        #     # AW: 风险处置对策
        #     sheet.cell(row=row_index, column=49, value=record.risk_treatment_strategy.name)
        #     sheet.cell(row=row_index, column=50, value=record.ecu.cal_level)  # CAL级别
        #     sheet.cell(row=row_index, column=51, value=record.risk_treatment_strategy.desc)  #
        #     sheet.cell(row=row_index, column=52, value='-')  # 原因备注
        #     # BA: 网络安全目标
        #     sheet.cell(row=row_index, column=53, value=f'CG-{task.abbr}-{cg_counter:04d}')  # 原因备注
        #     sheet.cell(row=row_index, column=54, value=record.cybersecurity_goal_name)  #
        #
        #     sheet.cell(row=row_index, column=55, value='')  #
        #     sheet.cell(row=row_index, column=56, value='')  #
        #
        #     sheet.cell(row=row_index, column=57, value=record.r_risk_est_r_level)  #
        #     sheet.cell(row=row_index, column=58,
        #                value=record.r_risk_treatment_strategy.name if record.r_risk_treatment_strategy else '')  #
        #
        #     for col in range(1, 58):
        #         target_cell = sheet.cell(row=row_index, column=col)
        #         target_cell.border = thin_border
        #         target_cell.font = Font(name='微软雅黑')
        #     row_index += 1

    try:
        # sheet.delete_cols(7)
        wb.save(template_file)

    except Exception as e:
        print(f'Failed to save file {template_file}: {e}')
        return False
    return True


def save_ecu_reqs_to_dict(pid, ecu_record):
    result = []
    # 定义CAL级别与字段的映射
    cal_field_mapping = {
        'CAL1': CybersecurityReqTable.cal1,
        'CAL2': CybersecurityReqTable.cal2,
        'CAL3': CybersecurityReqTable.cal3,
        'CAL4': CybersecurityReqTable.cal4,
    }
    ecu = ecu_record
    cal_level = ecu.cal_level
    project = ProjectTable.query.get(pid)
    if project is None:
        print(f'Error: Project {pid} not found')
        return False

    if cal_level and cal_level in cal_field_mapping:
        # 根据CAL级别查询对应的网络安全需求
        reqs = CybersecurityReqTable.query.filter(
            and_(
                CybersecurityReqTable.project_id == pid,
                cal_field_mapping[cal_level].isnot(None)
            )
        ).all()
        for req in reqs:
            if is_match_cal_req(req, ecu):
                level = getattr(req, cal_level.lower(), None)
                result.append({
                    'id': req.id,
                    'vendor': ecu.vendor,
                    'name': ecu.name,
                    'abbr': ecu.abbr_ext,
                    'type': req.type,
                    'code_or': req.code_or,
                    'name_or': req.name_or,
                    'desc_or': req.desc_or,
                    'code_dr': req.code_dr,
                    'name_dr': req.name_dr,
                    'desc_dr': req.desc_dr,
                    'level': level,
                    'cal': '√',
                    'tara': ''
                })

    # TARA 需求：不同供应商的 相同ECU使用相同的需求
    ecu_records = EcuTable.query.filter_by(Project_id=pid, abbr=ecu.abbr).all()
    for ecu_record in ecu_records:
        ras = (RiskAssessmentEcuTable.query.filter_by(project_id=pid, ecu_id=ecu_record.id)
               .distinct(RiskAssessmentEcuTable.cybersecurity_req_id).all())
        for ra in ras:
            if ra.cybersecurity_req:
                reqs = CybersecurityReqTable.query.filter_by(name_or=ra.cybersecurity_req.name_or,
                                                             project_id=pid).all()
                for req in reqs:
                    # 检查 result 中是否已经存在相同的 req.id
                    found = False
                    for item in result:
                        if item['id'] == req.id:
                            item['tara'] = '√'  # 如果存在，更新 tara 字段
                            found = True
                            # print(f'重复项: {req.id} - {req.code_dr} - {req.name_dr}')
                            break
                    if not found:
                        # 如果不存在，新增一条记录
                        result.append({
                            'id': req.id,
                            'vendor': ecu.vendor,
                            'name': ecu.name,
                            'abbr': ecu.abbr_ext,
                            'type': req.type,
                            'code_or': req.code_or,
                            'name_or': req.name_or,
                            'desc_or': req.desc_or,
                            'code_dr': req.code_dr,
                            'name_dr': req.name_dr,
                            'desc_dr': req.desc_dr,
                            'level': '强制',
                            'cal': '',  # 初始化 cal 字段为空
                            'tara': '√'
                        })
    print(f'{ecu.abbr_ext}: reqs = {len(result)}')
    ecu.remark = len(result)
    db.session.commit()
    return result

def save_all_reqs_to_dict(pid):
    result = []

    ecus = EcuTable.query.filter_by(Project_id=pid).all()
    for ecu in ecus:
        ret = save_ecu_reqs_to_dict(pid, ecu)
        result.extend(ret)
        # print(f'result count: {len(result)}, {len(ret)}')
    return result

def export_ecu_reqs(template_file, file_output, ecu, pid):
    """
    导出 某个 ECU的所有安全需求到excel
    """
    sheet_name = '零部件网络安全需求列表'
    # 设置边框
    thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))

    result = save_ecu_reqs_to_dict(pid, ecu)

    # 加载模板文件
    try:
        wb = load_workbook(template_file)
        sheet = wb[sheet_name]
    except Exception as e:
        print(f'Failed to load template file: {e}')
        return False  #
    # 将Result -> excel模板
    row_index = 5
    sheet.delete_rows(row_index, 4)
    task = ProjectTable.query.get(pid)
    sheet.cell(row=1, column=2, value=task.abbr)
    sheet.cell(row=1, column=6, value=f'{ecu.name} ({ecu.abbr})')
    sheet.cell(row=2, column=2, value=ecu.vendor)
    sheet.cell(row=2, column=6, value=ecu.cal_level)

    for item in result:
        for col in range(1, sheet.max_column):
            target_cell = sheet.cell(row=row_index, column=col)
            target_cell.border = thin_border
            target_cell.font = Font(name='微软雅黑', size=12)

        sheet.row_dimensions[row_index].height = 26

        sheet.cell(row=row_index, column=1, value=item['type'])
        sheet.cell(row=row_index, column=2, value=item['code_or'])
        sheet.cell(row=row_index, column=3, value=item['name_or'])
        sheet.cell(row=row_index, column=4, value=item['desc_or'])
        sheet.cell(row=row_index, column=5, value=item['code_dr'])
        sheet.cell(row=row_index, column=6, value=item['name_dr'])
        sheet.cell(row=row_index, column=7, value=item['desc_dr'])
        sheet.cell(row=row_index, column=8, value=item['level'])  # 需求级别
        sheet.cell(row=row_index, column=11, value=item['cal'])  # 需求来源 CAL
        sheet.cell(row=row_index, column=12, value=item['tara'])  # 需求来源

        row_index += 1

    # 保存到文件
    try:
        wb.save(file_output)
        ecu.remark = str(row_index - 5)
        db.session.commit()
    except Exception as e:
        print(f'Failed to save file {file_output}: {e}')
    finally:
        del wb  # 释放内存
    # time.sleep(1)
    return True


def export_all_reqs(file_name,  pid):
    # template_file = 'export_templates/零部件安全需求分配表模板.xlsx'
    template_file = 'export_templates/07 中国重汽 XXX车型 整车TARA需求表 v2.0.xlsx'
    sheet_name = '整车TARA需求表'#'表1-零部件网络安全需求列表'
    print(f'Start...export_all_reqs')

    # 设置边框
    thin_border = Border(left=Side(style='thin'),
                         right=Side(style='thin'),
                         top=Side(style='thin'),
                         bottom=Side(style='thin'))

    # 加载模板文件
    try:
        wb = load_workbook(template_file)
        sheet = wb[sheet_name]
    except Exception as e:
        print(f'Failed to load template file: {e}')
        return False  #

    result = save_all_reqs_to_dict(pid)
    print(f'save_all_reqs_to_dict return {len(result)} records...')
    # 将Result -> excel模板
    row_index = 3
    sheet.delete_rows(row_index, 3)
    for item in result:
        for col in range(1, sheet.max_column):
            target_cell = sheet.cell(row=row_index, column=col)
            target_cell.border = thin_border
            target_cell.font = Font(name='微软雅黑')

        sheet.row_dimensions[row_index].height = 26

        sheet.cell(row=row_index, column=1, value=item['vendor'])
        sheet.cell(row=row_index, column=2, value=item['name'])
        sheet.cell(row=row_index, column=3, value=item['abbr'])

        sheet.cell(row=row_index, column=4, value=item['type'])
        sheet.cell(row=row_index, column=5, value=item['code_or'])
        sheet.cell(row=row_index, column=6, value=item['name_or'])
        sheet.cell(row=row_index, column=7, value=item['desc_or'])
        sheet.cell(row=row_index, column=8, value=item['code_dr'])
        sheet.cell(row=row_index, column=9, value=item['name_dr'])
        sheet.cell(row=row_index, column=10, value=item['desc_dr'])
        sheet.cell(row=row_index, column=11, value=item['level']) #需求级别
        sheet.cell(row=row_index, column=12, value=item['cal']) #需求来源 CAL
        sheet.cell(row=row_index, column=13, value=item['tara']) #需求来源

        row_index += 1

    # 保存到本地文件
    if row_index > 4:
        # output_file = f'{file_name}.xlsx'
        try:
            wb.save(file_name)
        except Exception as e:
            print(f'Failed to save file {file_name}: {e}')
        finally:
            del wb  # 释放内存
    # time.sleep(1)
    return True

def export_safety_req_by_ecu(file_name, pid):
    # 设置边框
    thin_border = Border(left=Side(style='thin'),
                         right=Side(style='thin'),
                         top=Side(style='thin'),
                         bottom=Side(style='thin'))

    # 遍历 ECU
    try:
        ecus=EcuTable.query.filter_by(Project_id=pid).all()
        if ecus:
            for ecu in ecus:
                # 获取所有网络安全需求
                ras = RiskAssessmentEcuTable.query.filter_by(project_id=pid, ecu_id=ecu.id).all()
                if ras:
                    # 加载模板文件
                    wb = load_workbook('export_templates/零部件网络安全需求清单 导出模版v1.0.xlsx')
                    sheet = wb['零部件网络安全需求清单']
                    row_index = 5

                    for row in sheet.iter_rows():
                        for cell in row:
                            if not isinstance(cell, openpyxl.cell.MergedCell):
                                cell.value = replace_placeholders(cell.value, ecu.name, ecu.abbr)

                    for ra in ras:

                        if ra.cybersecurity_goal_name and ra.cybersecurity_req_id:
                            print(
                                f'export_safety_req_by_ecu: 安全目标={ra.cybersecurity_goal_name}， req id={ra.cybersecurity_req_id}')
                            req = ra.cybersecurity_req
                            sheet.insert_rows(row_index + 1)
                            for col in range(1, sheet.max_column + 1):
                                target_cell = sheet.cell(row=row_index + 1, column=col)
                                target_cell.border =thin_border
                                target_cell.font = Font(name='微软雅黑')
                            # copy_row_format(sheet, row, row+1)
                            sheet.cell(row=row_index, column=2, value=req.code_or)
                            sheet.cell(row=row_index, column=3, value=req.name_or)
                            sheet.cell(row=row_index, column=4, value=req.desc_or)
                            sheet.cell(row=row_index, column=5, value=req.code_dr)
                            sheet.cell(row=row_index, column=6, value=req.name_dr)
                            sheet.cell(row=row_index, column=7, value=req.desc_dr)
                            sheet.cell(row=row_index, column=9, value=ra.cybersecurity_goal_name)
                            sheet.cell(row=row_index, column=8, value=ra.cybersecurity_goal_code)
                            row_index += 1
                            print(f'OR code: {req.code_or}')
                    # 保存到本地文件
                    if row_index > 5:
                        wb.save(f'{file_name}_{ecu.abbr}.xlsx')


    except Exception:
        print(f'export_safety_req_by_ecu() error: {str(Exception)}')

    print(f'_____ 结束 __________')

    # 保存到内存中
    # output = BytesIO()
    # wb.save(output)
    # output.seek(0)
    # return f'Export completed. Check the file: {file_name}'
    # 返回文件
    # return send_file(output, as_attachment=True, download_name='零部件网络安全需求清单.xlsx')

#######################################################################
def update_cache_threat_analysis(threat_analysis_item, field, value):
    result = ThreatAnalysisShareTable.query.filter_by(
            ecu_abbr=threat_analysis_item.ecu.abbr,
            feature_name=threat_analysis_item.ecu_feature.feature,
            security_attr_name=threat_analysis_item.security_attr.name).first()


    if result:
        setattr(result, field, value)
        auto_calc_attack_level(result)
        db.session.commit()
        print(f'同步threat_analysis经验库成功: id={result.id}, field={field}, value={value}')
    else:
        print(f'同步threat_analysis经验库失败: {threat_analysis_item}, field={field}, value={value}')





def update_cache_risk_assessment(record, field, value):
    result = RiskAssessmentShareTable.query.filter_by(
                item_name=record.item.name,
                ecu_abbr=record.ecu.abbr,
                feature_name=record.ecu_feature.feature,
                security_attr_name=record.security_attr.name
                ).first()
    s = 'cybersecurity_req_id'
    if result:
        print(f'field={field}, s={s}, value={value}')
        if field=='cybersecurity_req_id':
            cs_req = CybersecurityReqTable.query.get(value)
            print(f'{result.id} -> cs_req={cs_req.id}, {cs_req.name_or}')
            setattr(result, 'cybersecurity_req_name', cs_req.name_or)

        else:
            print(f'更新到经验库: id={record.id}, field={field}, value={value}')
            setattr(result, field, value)
            auto_calc_risk_level(result)

        db.session.commit()
        print(f'同步到经验库RiskAssessmentShare成功: id={result.id}, field={field}, value={value}')
    else:
        print(f'同步到经验库RiskAssessmentShare失败: {record}, field={field}, value={value}')


def auto_ai_process_1():
    client = ZhipuAI(api_key=zhipu_key)  # 请填写您自己的APIKey

    # items = ThreatAnalysisTable.query.all()
    items = ThreatAnalysisTable.query.limit(15).all()
    total_items = len(items)
    processed_items = 0
    for item in items:
        try:
            # 获取相关属性
            ecu = item.ecu.name
            feature = item.ecu_feature.feature
            security_attr = item.security_attr.name
            thread_type = item.thread_type.name
            thread_scenario=''

            # 生成问题内容
            ask_content = f'针对汽车{ecu}的{feature}特性，威胁类型为{thread_type},安全属性为{security_attr}, 给出最可能的攻击路径（不超过100字）'

            # 调用 AI 模型生成攻击路径
            response = client.chat.completions.create(
                model=ai_model,
                messages=[
                    {"role": "system", "content": "你是一个汽车网络安全领域专家，帮助用户解答问题。例如用户问针对汽车硬件的机密性（威胁类型是信息泄露）的主要攻击路径是什么；可以回答：攻击者通过管脚信息、丝印等获取组件的外漏的硬件信息"},
                    {"role": "user", "content": ask_content}
                ],
                temperature=0.7
            )
            # 提取 AI 模型回答的威胁场景
            # print(f'response = {response}')
            if hasattr(response.choices[0].message, 'content'):
                attack_path = response.choices[0].message.content
            else:
                raise ValueError("Unexpected response structure: 'content' attribute not found.")

            # 将回答存储到 item 对象中
            item.attack_path = attack_path

            ask_content = f'针对汽车{ecu}的{feature}特性，威胁类型为{thread_type},安全属性为{security_attr}, 给出威胁最大的2个威胁场景（不超过100字）'

            # 调用 AI 模型生成答案
            response = client.chat.completions.create(
                model=ai_model,
                messages=[
                    {"role": "system", "content": "你是一个汽车网络安全领域专家，帮助用户解答问题。"},
                    {"role": "user", "content": ask_content}
                ],
                temperature=0.6
            )
            thread_scenario = response.choices[0].message.content
            item.thread_scenario = thread_scenario

            ask_content = f'针对汽车{ecu}的{feature}特性(威胁类型为{thread_type},安全属性为{security_attr}, 攻击路径为"{attack_path}",威胁场景为"{thread_scenario}"),评估攻击者发现漏洞所需要的时间：如果所需时间小于1天，则返回数字1;如果所需时间大于1天且小于1周，则返回数字2;如果所需时间大于1周且小于1个月，则返回数字3;如果所需时间大于1个月且小于6个月，则返回数字4;如果所需时间大于6个月，则返回数字5'

            # 调用 AI 模型生成答案
            response = client.chat.completions.create(
                model=ai_model,
                messages=[
                    {"role": "system",
                     "content": "你是一个汽车网络安全领域专家，用数字编号答复用户攻击者发现漏洞所需要的时间：1代表小于1天，2代表超过1天且小于1周，3代表超过1周且小于1个月，4代表超过1个月且小于6个月，5代表超过6个月.\
                                如果有多个可能的威胁场景, 则返回所需时间最短的编号即可, 答复中只有一个数字编号,不需要给出详细的解释 \
                                例如用户问:针对汽车硬件的机密性（威胁类型是信息泄露）的主要威胁场景(1、攻击者通过非法接入硬件预留的调试接口获取数据；\
                                2、攻击者通过暴露的PBC管脚获取芯片管脚功能信息，为非法接入提供条件；3、攻击者通过丝印获取芯片信息，如管脚功能等)\
                                 攻击者发现漏洞所需要的时间是多长，用编号答复。 你应该答复 1 ; \
                                   针对汽车操作系统的机密性（威胁类型是信息泄露）的主要威胁场景(1、攻击者收集组件版本号等系统信息为后续攻击做准备；\
                                            2、攻击者通过外部接口利用漏洞获得系统账号和密码 3、攻击者利用木马等恶意软件侵入系统，获得系统与其他组件的交互信息) \
                                 用户问攻击者发现漏洞所需要的时间是多长，用编号答复。 你应该答复 3 "},
                    {"role": "user", "content": ask_content}
                ],
                temperature=0.5
            )
            result_index = response.choices[0].message.content
            print(f' -发现漏洞所需要的时间: #{result_index}#')
            item.vdt_id = int(result_index)

            # 打印结果
            print(f" {ecu} - {feature} - {security_attr} : \n攻击路径 {attack_path} \n威胁场景 {thread_scenario} ")
            # 更新已处理项目数
            processed_items += 1
            # 计算并打印进度
            progress = (processed_items / total_items) * 100
            print(f"--------------------- Progress: {progress:.2f}% -----------------------------------------------")

        except Exception as e:
            # 处理错误
            print(f"Error processing item: {item}, Error: {e}")
    db.session.commit()
def create_item_table_uml(pid):
    uml_path = f'static/plantuml/{pid}'
    if not os.path.exists(uml_path):
        os.makedirs(uml_path)

    items = ItemTable.query.filter_by(project_id=pid).all()
    for item in items:
        if item.plant_uml and '[' in item.plant_uml:
            create_uml_picture(item.plant_uml, item.name, item.id, pid)

    print(f'create uml picture finished.')


def auto_plant_uml(item):
    global client
    global ai_model

    # if not internet_status or zhipu == 0:
    #     return item
    if item and item.plant_uml and len(item.plant_uml) > 4:
        #已经有了，跳过
        print(f'skip: {item.plant_uml}')
        return 1
    try:
        # 获取相关属性
        data_flow = item.data_flow
        # 生成问题内容
        question = f'基于数据流[{data_flow}], 生成PlantUML 代码(不需要返回@startuml 和 @enduml)'

        response = client.chat.completions.create(
            model=ai_model,
            messages=[
                {"role": "system",
                 "content": """ 你是PlantUML专家，根据提供的信息生成PlantUML 组件图代码. 注意不需要返回@startuml 和 @enduml .
                 例如输入信息是<遥控钥匙—>IBCM—>四门锁>, 生成的组件图代码: [遥控钥匙]->[IBCM]\n [IBCM]->[四门锁]  
                 第二个例子, 输入信息是<IBCM—>GW—>IP,IBCM—>GW—>IP,IBCM—>GW—>IP, IBCM—>GW—>AC>, 生成的组件图代码去掉重复的部分,返回: [IBCM]->[GW]\n [GW]->[IP]\n [GW]->AC 
                """},

                {"role": "user", "content": question}
            ],
            temperature=0.5
        )
        # 提取 AI 模型回答的威胁场景
        print(f"问题: {question}")

        reply_message = response.choices[0].message.content
        print(f"reply_message = {reply_message}")


        item.plant_uml = reply_message

        db.session.commit()
        return 0
    except Exception as e:
        # 处理错误
        print(f"Error processing: {item}, Error: {e}")


def threat_analysis_table_process(item, __t):
    global client
    global ai_model

    if not internet_status or zhipu==0:
        # print(f'threat_analysis_table_process::::::::: 不满足条件, 返回')
        return item

    if item:
        try:
            # 获取相关属性
            ecu = item.ecu_name
            feature = item.feature_name
            security_attr = item.security_attr_name
            thread_type = item.thread_type_name
            thread_scenario = ''

            # 生成问题内容
            question = f'针对汽车{ecu}的{feature}特性，威胁类型为{thread_type},安全属性为{security_attr}, 按Json格式答复 攻击路径，威胁场景 ,攻击者发现漏洞所需要的时间, 攻击者所需的专业水平, 攻击所需的知识度水平, 攻击所利用的机会窗口, 攻击所需设备水平'

            # 调用 AI 模型生成攻击路径
            response = client.chat.completions.create(
                model=ai_model,
                messages=[
                    {"role": "system", "content": '你是一个汽车网络安全领域专家，用Json格式回答用户的问题。具体格式如下： \
                                    { \
                                        "攻击路径": 答复最可能得攻击路径(不超过100字), \
                                        "威胁场景": 答复威胁最大的2个威胁场景（不超过100字）, \
                                        "攻击者发现漏洞所需要的时间": 用编号答复(例如 大于6个月，则答复 4 ),\
                                        "攻击者所需的专业水平": 用编号答复(例如需要此领域的专家水平，则答复 3) ,\
                                        "攻击所需的知识度水平": 用编号答复(例如需要获取机密信息，则答复3),\
                                        "攻击所利用的机会窗口": 用编号答复(例如很容易获得机会窗口则答复2) ,\
                                        "攻击所需设备水平": 用编号答复(例如只需要标准的设备则答复编号1) \
                                    } \
                                    其中"攻击者发现漏洞所需要的时间" 的答复选项是  {{1: 小于1天}, {2: 大于1天小于1个月}, {3: 大于1个月小于6个月}, {4: 大于6个月}} ,\
                                    其中"攻击者所需的专业水平" 的答复选项是  {{1: 外行即可}, {2: 精通某个领域}, {3: 某个领域的专家}, {4: 多个领域的专家}} ,\
                                    其中"攻击所需的知识度水平" 的答复选项是  {{1: 被攻击的资产信息是公开的}, {2: 非公开/受限的}, {3:被攻击的资产信息是机密 }, {4:被攻击的资产信息是高度机密 }} ,\
                                    其中"攻击所利用的机会窗口" 的答复选项是  {{1: 任意时间都可以进行攻击(无限制)}, {2: 很容易获得机会窗口}, {3: 攻击的机会窗口是中等(即不是很容易但也不困难)}, {4: 攻击的机会窗口是困难或不可能}}, \
                                    其中"攻击所需设备水平" 的答复选项是  {{1: 只需要标准的设备}, {2: 需要做简单的改装，不需要单独定制特殊工具}, {3: 需要特殊定制的设备或工具}, {4: 需要多个特殊定制的设备}} \
                                     '},

                    {"role": "user", "content": question}
                ],
                temperature=__t
            )
            # 提取 AI 模型回答的威胁场景

            reply_message = response.choices[0].message.content
            print(f"问题: {question}")
            print(f"reply_message = {reply_message}")

            data = json.loads(reply_message)
            # 访问解析后的数据
            attack_path = data['攻击路径']
            threat_scenario = data['威胁场景']
            time_to_find_vulnerability = data['攻击者发现漏洞所需要的时间']
            time_2 = data['攻击者所需的专业水平']
            time_3 = data['攻击所需的知识度水平']
            time_4 = data['攻击所利用的机会窗口']
            time_5 = data['攻击所需设备水平']

            # 打印结果
            print("攻击路径:", attack_path)
            print("威胁场景:", threat_scenario)
            print(f"攻击者发现漏洞所需要的时间: {time_to_find_vulnerability}, {time_2}, {time_3}, {time_4}, {time_5}")

            item.attack_path = attack_path
            item.thread_scenario = threat_scenario

            item.vdt_id = int(time_to_find_vulnerability)
            item.asl_id = 5 + int(time_2)
            item.knowledge_level_id = 9 + int(time_3)
            item.woo_id = 13 + int(time_4)
            item.device_id = 17 + int(time_5)

            db.session.commit()
            auto_calc_attack_level(item)
        except Exception as e:
            # 处理错误
            print(f"Error processing threat_analysis_table: {item}, Error: {e}")


            # db.session.delete(item)
    return item

def auto_ai_process_1_json(f_temperature):
    # items = ThreatAnalysisTable.query.all()
    items = ThreatAnalysisTable.query.limit(20).all()
    total_items = len(items)
    processed_items = 0
    for item in items:
        threat_analysis_table_process(item.id, f_temperature)
        # 更新已处理项目数
        processed_items += 1
        # 计算并打印进度
        progress = (processed_items / total_items) * 100
        print(f"--------------------- Progress: {progress:.2f}% -----------------------------")

def auto_process_item_desc(pid):
    global client
    global ai_model
    global internet_status

    items = ItemTable.query.filter_by(project_id=pid).all()
    # total_items = len(items)
    for item in items:
        auto_plant_uml(item)
        if item.plant_uml and '[' in item.plant_uml:
            create_uml_picture(item.plant_uml, item.name, item.id, pid)


        if item.desc and len(item.desc) > 10:
            try:
                # 生成问题内容
                question = f'针对功能[{item.name}]，输入信息如下: “{item.desc}” '
                system_tips = """
                    你是一个汽车领域专家，针对车辆的某个功能, 基于用户提供的输入描述信息，总结答复其功能描述(80字以内)
                """
                # 调用 AI 模型生成损害场景描述
                response = client.chat.completions.create(
                    model=ai_model,
                    messages=[
                        {"role": "system", "content": system_tips},
                        {"role": "user", "content": question}
                    ],
                    temperature=0.6
                )

                # 提取 AI 模型回答的损害场景描述
                reply_message = response.choices[0].message.content
                colon_index = reply_message.find('：')  # 找到冒号的索引
                if colon_index != -1:  # 如果找到冒号
                    item.desc = reply_message[colon_index + 1:]  # 返回冒号之后的部分
                else:
                    item.desc = reply_message

                print(f"问题: {question}")
                print(f" reply_message = {reply_message}")

                db.session.commit()

            except Exception as e:
                # 处理错误
                print(f"Error auto_process_item_desc({item.name}) : {e}")

    print(f'Item Description auto process complete.')
    return
def risk_assessment_table_process(record, __t):
    global client
    global ai_model
    global internet_status

    # record = RiskAssessmentEcuTable.query.get(record_id)
    if not internet_status or zhipu == 0:
        return record

    if record:
        print(f'risk_assessment 自动处理:  id={record.id}, threat_analysis_id={record.threat_analysis_id}')
        try:
            # 获取相关属性
            item = record.item_name
            # item_desc = record.item_desc
            ecu = record.ecu_name
            abbr = record.ecu_abbr
            feature = record.feature_name
            security_attr = record.security_attr_name
            thread_type=record.threat_analysis.thread_type_name
            # 生成问题内容
            question = f'针对汽车功能 "{item}"中的零部件 <{abbr}-{ecu}> 的特性" {feature}",分析其安全属性为 "{security_attr}", 威胁类型为"{thread_type}"的最可能的 损害场景描述，安全影响，经济影响，操作影响，隐私影响, 按预定义的Json格式答复 '

            # 调用 AI 模型生成损害场景描述
            response = client.chat.completions.create(
                model=ai_model,
                messages=[
                    {"role": "system",
                     "content": '你是一个汽车网络安全领域专家，用Json格式回答用户的问题(大括号)。具体格式如下： \
                                                { \
                                                    "损害场景描述": 答复最可能的损害场景描述(不超过100字), \
                                                    "安全影响": 答复安全影响程度的编号，例如轻微或中度伤害，则答复 1), \
                                                    "经济影响": 用编号答复(例如轻微的，通常是10000元以内，则答复 1), \
                                                    "操作影响": 用编号答复(例如轻微的，车辆功能仍然可以正常操作，则答复 1), \
                                                    "隐私影响": 用编号答复(例如可忽略的，对隐私没有产生影响，则答复 1) \
                                                } \
                                                <补充说明>:    \
                                                其中"安全影响" 的答复选项含义  [{1: 轻微或中度伤害}, {2: 严重受伤（可能幸存）或有多车引起的轻微或中度伤害}, {3: 危及生命或致命伤害（不确定能幸存）或由多车引起的严重伤害}, {4: 多车引起的危及生命或致命伤害}] ,\
                                                其中"经济影响" 的答复选项含义  [{1: 轻微的，通常是10000元以内}, {2: 中等损失，通常是10000以上，5万以内}, {3: 重大的，车辆需要大修或报废，通常是5万元以上}, {4: 严重的，车辆报废，且损坏了很多其它的财务}],\
                                                其中"操作影响" 的答复选项含义  [{1: 轻微的，车辆功能仍然可以正常操作}, {2: 中等的，车辆的辅助、娱乐等功能退化或失效（如车辆中控屏黑屏，或收音机发出无法关闭的噪音）}, {3: 重大的，车辆的主要功能退化（如车辆进入陂行模式、ADAS功能降级运行）}, {4: 严重的，车辆的主要功能失效（如车辆无法启动、行车中丢失动力、ADAS系统完全无法工作）或：车辆的部分功能失效，且该失效会导致违反监管法规（如排放、环保、EDR等车辆或系统的型式认证法规）}] ,\
                                                其中"隐私影响" 的答复选项含义  [{1: 可忽略的，对隐私没有产生影响}, {2: 中等，给道路使用者带来极大的不便（例如单个人员的隐私数据泄露 ）}, {3: 重大级别，会严重影响道路使用者（如个别隐私数据被泄露且被滥用，或引起个别媒体报道）}, {4: 严重级别，会给道路使用者带来重大甚至不可逆转的影响（如大批量隐私数据被泄露，或引发媒体广泛报道，或引起隐私保护监管机构的调查和罚款）}] \
                                                 '},
                    {"role": "user", "content": question}
                ],
                temperature=__t
            )

            # 提取 AI 模型回答的损害场景描述
            reply_message = response.choices[0].message.content
            # print(f"问题: {question}")
            print(f" reply_message = {reply_message}")

            data = json.loads(reply_message)
            # 访问解析后的数据
            damage_scenario_desc = data['损害场景描述']
            safe_affect_id = int(data['安全影响'])
            economy_affect_id = int(data['经济影响'])
            operator_affect_id = int(data['操作影响'])
            private_affect_id = int(data['隐私影响'])

            # 打印结果
            print("损害场景描述:", damage_scenario_desc)
            print(
                f"安全影响: {safe_affect_id}, 经济影响: {economy_affect_id}, 操作影响: {operator_affect_id}, 隐私影响: {private_affect_id}")

            record.damage_scenario_desc = damage_scenario_desc
            record.safe_affect_id = safe_affect_id
            record.economy_affect_id = economy_affect_id + 4
            record.operator_affect_id = operator_affect_id + 8
            record.private_affect_id = private_affect_id + 12

            auto_calc_risk_level(record)
            db.session.commit()

        except Exception as e:
            # 处理错误
            print(f"Error risk_assessment_table_process() : {e}")

    return record

def auto_ai_process_risk_assessment_table_json(val_temperature):
    # client = ZhipuAI(api_key=zhipu_key)  #
    global  client

    records = RiskAssessmentEcuTable.query.all()
    # records = RiskAssessmentEcuTable.query.limit(8).all()
    total_items = len(records)
    processed_items = 0

    for record in records:
        try:
            # 获取相关属性
            item = record.item.name
            ecu = record.ecu.abbr
            feature = record.ecu_feature.feature
            security_attr = record.security_attr.name
            thread_scenario = record.threat_analysis.thread_scenario
            damage_scenario = record.damage_scenario_desc  # 损害场景

            # 生成问题内容
            question = f'针对汽车网络安全属性为{security_attr}的 {item} {ecu}的{feature} 的威胁场景进行分析, 按Json格式答复 损害场景描述，安全影响，经济影响，操作影响，隐私影响'

            # 调用 AI 模型生成损害场景描述
            response = client.chat.completions.create(
                model=ai_model,
                messages=[
                    {"role": "system",
                     "content": '你是一个汽车网络安全领域专家，用Json格式回答用户的问题。具体格式如下： \
                                    { \
                                        "损害场景描述": 答复最可能的损害场景描述(不超过100字), \
                                        "安全影响": 用编号答复(例如轻微或中度伤害，则答复 1), \
                                        "经济影响": 用编号答复(例如轻微的，通常是10000元以内，则答复 1), \
                                        "操作影响": 用编号答复(例如轻微的，车辆功能仍然可以正常操作，则答复 1), \
                                        "隐私影响": 用编号答复(例如可忽略的，对隐私没有产生影响，则答复 1) \
                                    } \
                                    其中"安全影响" 的答复选项是  {{1: 轻微或中度伤害}, {2: 严重受伤（可能幸存）或有多车引起的轻微或中度伤害}, {3: 危及生命或致命伤害（不确定能幸存）或由多车引起的严重伤害}, {4: 多车引起的危及生命或致命伤害}} ,\
                                    其中"经济影响" 的答复选项是  {{1: 轻微的，通常是10000元以内}, {2: 中等损失，通常是10000以上，5万以内}, {3: 重大的，车辆需要大修或报废，通常是5万元以上}, {4: 严重的，车辆报废，且损坏了很多其它的财务}} ,\
                                    其中"操作影响" 的答复选项是  {{1: 轻微的，车辆功能仍然可以正常操作}, {2: 中等的，车辆的辅助、娱乐等功能退化或失效（如车辆中控屏黑屏，或收音机发出无法关闭的噪音）}, {3: 重大的，车辆的主要功能退化（如车辆进入陂行模式、ADAS功能降级运行）}, {4: 严重的，车辆的主要功能失效（如车辆无法启动、行车中丢失动力、ADAS系统完全无法工作）或：车辆的部分功能失效，且该失效会导致违反监管法规（如排放、环保、EDR等车辆或系统的型式认证法规）}} ,\
                                    其中"隐私影响" 的答复选项是  {{1: 可忽略的，对隐私没有产生影响}, {2: 中等，给道路使用者带来极大的不便（例如单个人员的隐私数据泄露 ）}, {3: 重大级别，会严重影响道路使用者（如个别隐私数据被泄露且被滥用，或引起个别媒体报道）}, {4: 严重级别，会给道路使用者带来重大甚至不可逆转的影响（如大批量隐私数据被泄露，或引发媒体广泛报道，或引起隐私保护监管机构的调查和罚款）}} \
                                     '},
                    {"role": "user", "content": question}
                ],
                temperature=val_temperature
            )

            # 提取 AI 模型回答的损害场景描述
            reply_message = response.choices[0].message.content
            print(f"问题: {question}")
            print(f"reply_message = {reply_message}")

            data = json.loads(reply_message)
            # 访问解析后的数据
            damage_scenario_desc = data['损害场景描述']
            safe_affect_id = data['安全影响']
            economy_affect_id = data['经济影响']
            operator_affect_id = data['操作影响']
            private_affect_id = data['隐私影响']

            # 打印结果
            print("损害场景描述:", damage_scenario_desc)
            print(f"安全影响: {safe_affect_id}, 经济影响: {economy_affect_id}, 操作影响: {operator_affect_id}, 隐私影响: {private_affect_id}")

            record.damage_scenario_desc = damage_scenario_desc
            record.safe_affect_id = safe_affect_id
            record.economy_affect_id = economy_affect_id + 4
            record.operator_affect_id = operator_affect_id + 8
            record.private_affect_id = private_affect_id + 12

            db.session.commit()
            auto_calc_risk_level(record)

            # 更新已处理项目数
            processed_items += 1
            # 计算并打印进度
            progress = (processed_items / total_items) * 100
            print(f"--------------------- 已完成: {progress:.2f}% -----------------------------------------------")

        except Exception as e:
            # 处理错误
            print(f"Error processing item: {record}, Error: {e}")

    db.session.commit()



# 自动处理 风险评估表
def auto_ai_process_risk_assessment_ecu_table():
    client = ZhipuAI(api_key=zhipu_key)  # 请填写您自己的APIKey

    records = RiskAssessmentEcuTable.query.all()
    # records = RiskAssessmentEcuTable.query.limit(8).all()
    total_items = len(records)
    processed_items = 0
    for record in records:
        try:
            # 获取相关属性
            item = record.item.name
            ecu = record.ecu.abbr
            feature = record.ecu_feature.feature
            security_attr = record.security_attr.name
            thread_scenario = record.threat_analysis.thread_scenario
            damage_scenario = record.damage_scenario_desc  #损害场景

            ask_content = (f'针对汽车网络安全属性为{security_attr}的 {item} {ecu}的{feature} 的威胁场景进行分析, 给出最可能的损害场景描述，100字以内 ')

            # 损害场景
            response = client.chat.completions.create(
                model=ai_model,
                messages=[
                    {"role": "system",
                     "content": "你是一个汽车网络安全领域专家，基于汽车的某个部件的威胁场景进行分析，给出最有可能的损害场景描述。\
                                                         例如针对'汽车底盘智能域,中控屏,硬件（可用性）' 可能的威胁场景，分析得到其损害场景描述为 丧失可用性，导致车辆使用过程中信息显示不全或丧失，盲点检测、碰撞预警等监控预警功能没有产生应有的作用，影响驾驶员操作安全"},
                    {"role": "user", "content": ask_content}
                ],
                temperature=0.5
            )
            record.damage_scenario_desc = response.choices[0].message.content

            # 分析影响
            ask_content = f'针对汽车网络安全属性为{security_attr}的 {item} {ecu}的{feature} 的损害场景"{damage_scenario}", \
            评估对安全的影响大小，用int类型的数字答复: 1 代表"轻微或中度伤害", 2 代表"严重受伤（可能幸存）或有多车引起的轻微或中度伤害",\
             3 代表"危及生命或致命伤害（不确定能幸存）或由多车引起的严重伤害", 4 代表"多车引起的危及生命或致命伤害"'
            response = client.chat.completions.create(
                model=ai_model,
                messages=[
                    {"role": "system",
                     "content": "你是一个汽车网络安全领域专家，基于汽车的安全相关知识评估用户的损害场景对安全的影响大小，用int类型的编号答复，不需要详细的文字解释 "},
                    {"role": "user", "content": ask_content}
                ],
                temperature=0.5
            )
            safe_affect_id = int(response.choices[0].message.content)

            ask_content = f'针对汽车网络安全属性为{security_attr}的 {item} {ecu}的{feature} 的损害场景"{damage_scenario}", \
                        评估对经济的影响程度，用int类型的数字答复: 1 代表"轻微的，通常是10000元以内", 2 代表"中等损失，通常是10000以上，5万以内",\
                         3 代表"重大的，车辆需要大修或报废，通常是5万元以上", 4 代表"严重的，车辆报废，且损坏了很多其它的财务"'
            response = client.chat.completions.create(
                model=ai_model,
                messages=[
                    {"role": "system",
                     "content": "你是一个汽车网络安全领域专家，基于汽车的安全相关知识评估用户的损害场景对财产或经济的影响大小，用int类型的编号答复，不需要详细的文字解释 "},
                    {"role": "user", "content": ask_content}
                ],
                temperature=0.5
            )
            economy_affect_id = int(response.choices[0].message.content)

            ask_content = f'针对汽车网络安全属性为{security_attr}的 {item} {ecu}的{feature} 的损害场景"{damage_scenario}", \
                                    评估对车辆操作的影响程度，用int类型的数字答复: 1 代表"轻微的，车辆功能仍然可以正常操作", 2 代表"中等的，车辆的辅助、娱乐等功能退化或失效（如车辆中控屏黑屏，或收音机发出无法关闭的噪音）",\
                                     3 代表"重大的，车辆的主要功能退化（如车辆进入陂行模式、ADAS功能降级运行）", 4 代表"严重的，车辆的主要功能失效（如车辆无法启动、行车中丢失动力、ADAS系统完全无法工作）或：车辆的部分功能失效，且该失效会导致违反监管法规（如排放、环保、EDR等车辆或系统的型式认证法规）"'
            response = client.chat.completions.create(
                model=ai_model,
                messages=[
                    {"role": "system",
                     "content": "你是一个汽车网络安全领域专家，基于汽车的安全相关知识评估用户的损害场景对车辆操作的影响大小，用int类型的编号答复，不需要详细的文字解释 "},
                    {"role": "user", "content": ask_content}
                ],
                temperature=0.5
            )
            operator_affect_id = int(response.choices[0].message.content)

            ask_content = f'针对汽车网络安全属性为{security_attr}的 {item} {ecu}的{feature} 的损害场景"{damage_scenario}", \
                                                评估对隐私保护的影响程度，用int类型的数字答复: 1 代表"可忽略的，对隐私没有产生影响", 2 代表"中等，给道路使用者带来极大的不便（例如单个人员的隐私数据泄露 ）",\
                                                 3 代表"重大级别，会严重影响道路使用者（如个别隐私数据被泄露且被滥用，或引起个别媒体报道）", 4 代表"严重级别，会给道路使用者带来重大甚至不可逆转的影响（如大批量隐私数据被泄露，或引发媒体广泛报道，或引起隐私保护监管机构的调查和罚款）"'
            response = client.chat.completions.create(
                model=ai_model,
                messages=[
                    {"role": "system",
                     "content": "你是一个汽车网络安全领域专家，基于汽车的安全相关知识评估用户的损害场景对隐私的影响程度，用int类型的编号答复，不要文字解释 "},
                    {"role": "user", "content": ask_content}
                ],
                temperature=0.5
            )
            private_affect_id = int(response.choices[0].message.content)

            record.safe_affect_id = safe_affect_id
            record.operator_affect_id = operator_affect_id + 8
            record.economy_affect_id = economy_affect_id + 4
            record.private_affect_id = private_affect_id + 12
            db.session.commit()

            auto_calc_risk_level(record)


            # 打印结果
            print(f" {item},{ecu},{feature},{security_attr} : > {safe_affect_id}, {economy_affect_id}, {operator_affect_id}, {private_affect_id}")
            # 更新已处理项目数
            processed_items += 1
            # 计算并打印进度
            progress = (processed_items / total_items) * 100
            print(f"--------------------- 已完成: {progress:.2f}% -----------------------------------------------")

        except Exception as e:
            # 处理错误
            print(f"Error processing item: {record}, Error: {e}")
    db.session.commit()

def above2(threat_analysis):
    for ra in threat_analysis.risk_assessment_ecu_levels:
        if int(ra.risk_level) > 2:
            return True
    return False

def initdb():
    print(f"数据库初始化开始...")
    # 删除所有表
    with db.engine.connect() as connection:
        inspector = inspect(db.engine)
        for table_name in inspector.get_table_names():
            drop_table_sql = text(f'DROP TABLE IF EXISTS {table_name}')
            connection.execute(drop_table_sql)

        db.create_all()  # 创建所有表

        now = f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
        # 插入初始化数据
        print("Inserting random data into CustomerTable table...")
        record = CustomerTable(
            name=f"中国重型汽车集团有限公司",
            name_en=f"SINOTRUK",
            remark=f"中国重汽主要研发、生产、销售各种载重汽车、特种汽车、专用车及发动机、变速箱、车桥等总成和汽车零部件，拥有黄河、汕德卡、豪沃等全系列商用汽车品牌，是我国重卡行业驱动形式和功率覆盖最全的重卡企业之一。下属三个上市公司，分别为中国重汽（香港）有限公司（香港红筹公司）、中国重汽集团济南卡车股份有限公司（深圳A股上市公司）、中通客车股份有限公司（深圳A股上市公司）。产品出口110多个国家和地区，占据我国重卡出口的半壁江山。",
            create_time=now,
            update_time=now
        )
        db.session.add(record)
        record = CustomerTable(
            name=f"中国重汽集团济南卡车股份有限公司",
            name_en=f"SINOTRUK",
            remark=f"中国重型汽车集团有限公司的子公司，股票代码：000951 \n 主导产品：重型载重汽车，涵盖200多个品种，驱动形式从4×2到12×12，汽车总质量覆盖11吨-60吨。",
            create_time=now,
            update_time=now
        )
        db.session.add(record)
        record = CustomerTable(
            name=f"上汽大众汽车有限公司",
            name_en=f"SAIC VOLKSWAGEN AUTOMOTIVE CO., LTD.",
            remark=f"上汽大众汽车有限公司是一家中德合资企业，由上汽集团和大众汽车集团合资经营，中德双方投资比例各为50%。公司于1984年10月签约奠基，总部位于上海，并先后在上海、江苏、新疆、浙江、湖南等地建立了生产基地，累计产销超2800万辆，员工超2.6万名。",
            create_time=now,
            update_time=now
        )
        db.session.add(record)

        # 项目表
        Tasks = [
            {'name': '2024 R平台-纯电 安全认证', 'abbr': 'RE', 'prefix': 'CS', 'customer_id': 1, 'state': '进行中',
             'pm': '吴迪'},
            {'name': '2024 R平台-油 安全认证', 'abbr': 'RO', 'prefix': 'CS', 'customer_id': 1, 'state': '进行中',
             'pm': '吴迪'},
            {'name': '2024 FV70 安全认证', 'abbr': 'FV', 'prefix': 'CS', 'customer_id': 1, 'state': '待启动',
             'pm': '李民'},
            {'name': '2024 FP70 安全认证', 'abbr': 'FP', 'prefix': 'CS', 'customer_id': 1, 'state': '进行中',
             'pm': '李民'},
            {'name': '2024 U7x 安全认证', 'abbr': 'U7x', 'prefix': 'CS', 'customer_id': 1, 'state': '进行中',
             'pm': '李民'}
        ]
        for p in Tasks:
            record = ProjectTable(
                name=p['name'],
                abbr=p['abbr'],
                prefix=p['prefix'],
                desc=f'描述 ',
                customer_id=p['customer_id'],
                state=p['state'],
                pm=p['pm'],
                create_time=now,
                update_time=now
            )
            db.session.add(record)

        print("Inserting demo data into UserTable table...")
        record = UserTable(
            name=f"Admin",
            pwd=f"0000",
            role=1,
            project_id=1,
            state=1,
            phone=f"13999999999",
            email=f"admin@china.com",
            remark="系统管理员",
            create_time=now,
            update_time=now
        )
        db.session.add(record)
        record = UserTable(
            name=f"济南高新区",
            pwd=f"0000",
            role=1,
            customer_id=1,
            project_id=1,
            state=1,
            phone=f"13999999999",
            email=f"zhangsan@china.com",
            remark="操作员",
            create_time=now,
            update_time=now
        )
        db.session.add(record)
        record = UserTable(
            name=f"重汽子公司",
            pwd=f"0000",
            role=2,
            customer_id=2,
            project_id=3,
            state=1,
            phone=f"137777777777",
            email=f"子公司@china.com",
            remark="操作员",
            create_time=now,
            update_time=now
        )
        db.session.add(record)

        # 基础数据表
        SecurityAttrs = [
            {'name': '可用性', 'desc': '描述'},
            {'name': '完整性', 'desc': '描述'},
            {'name': '机密性', 'desc': '描述'},
            {'name': '真实性', 'desc': '描述'},
            {'name': '不可抵赖性', 'desc': '描述'},
            {'name': '身份授权', 'desc': '描述'},
            {'name': '隐私', 'desc': '描述'}
        ]
        for item in SecurityAttrs:
            record = SecurityAttrTable(
                name=item['name'],
                desc=f"{item['name']}{item['desc']}",
            )
            db.session.add(record)
        db.session.commit()
        # 威胁类型
        Threads = [
            {'name': '拒绝服务', 'SA': '可用性', 'desc': '描述'},
            {'name': '篡改', 'SA': '完整性', 'desc': '描述'},
            {'name': '信息泄露', 'SA': '机密性', 'desc': '描述'},
            {'name': '冒充', 'SA': '真实性', 'desc': '描述'},
            {'name': '抵赖', 'SA': '不可抵赖性', 'desc': '描述'},
            {'name': '权限提升', 'SA': '身份授权', 'desc': '描述'},
            {'name': '泄露', 'SA': '隐私', 'desc': '描述'}
        ]
        for item in Threads:
            record = ThreadTypeTable(
                name=item['name'],
                security_attr=item['SA'],
                desc=f"{item['SA']}{item['desc']}",
            )
            db.session.add(record)

        # 4.2 风险处置策略表
        RTSs = [
            {'name': '风险接受', 'desc': '基于网络安全风险评估结果，风险水平可接受'},
            {'name': '风险转移', 'desc': '基于网络安全风险评估结果，风险水平可接受'},
            {'name': '风险回避', 'desc': ''},
            {'name': '风险降低', 'desc': ''}
        ]
        for item in RTSs:
            record = RiskTreatmentStrategyTable(
                name=item['name'],
                desc=item['desc']
            )
            db.session.add(record)


        db.session.commit()

        return "Database initialized successfully!"

def test_internet():
    global internet_status
    internet_status = is_connected_to_internet('http://www.baidu.com')
    return internet_status

def get_project_names():
    """Return a list of project dictionaries containing id and name"""
    try:
        projects = ProjectTable.query.all()
        return [{"id": project.id, "name": project.name} for project in projects]
    except SQLAlchemyError as e:
        print(f"Database error: {e}")
        return []

# 需求类别
requirement_types = [
    "硬件",
    "系统软件",
    "应用软件",
    "数据",
    "内部通信",
    "无线通信",
    "物理接口",
    "重要功能",
    "通用要求",
]

# 在Word文档中的指定表格后面插入一个新表格。
# :param doc: Document对象，表示Word文档
# :param old_table: Table对象，表示要在其后插入新表格的表格
def add_new_table_after_table(doc, old_table):
    # 在旧表格之后添加一个空行
    empty_paragraph = doc.add_paragraph()
    empty_paragraph.add_run(' ')

    # 在空行后插入一个新的表格
    new_table = doc.add_table(rows=5, cols=2)
    # 设置表格样式
    new_table.style = 'Table Grid'

    #对新表格第一列进行初始化赋值
    new_table.cell(0, 0).text = "安全目标编号"
    new_table.cell(1, 0).text = "需求编号"
    new_table.cell(2, 0).text = "需求名称"
    new_table.cell(3, 0).text = "需求描述"
    new_table.cell(4, 0).text = "需求分配的ECU"

    # 将新表格插入到old_table后面
    old_table._element.addnext(new_table._element)
    # 将新表格插入到空行之后
    new_table._element.addprevious(empty_paragraph._element)

    # # 设置表格额边框为细实线
    for row in new_table.rows:
        for cell in row.cells:
            set_thin_solid_border(cell)
            cell.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
            # 表格中字体设置为11号，宋体
            for paragraph in cell.paragraphs:
                for run in paragraph.runs:
                    run.font.size = Pt(11)
                    run.font.name = '宋体'
        row.height = Inches(0.3)
    # 设置表格的缩进
    new_table.alignment = 0  # 0表示左对齐，1表示居中，2表示右对齐
    # new_table.autofit = False  # 禁用自动调整表格宽度
    # new_table.allow_autofit = False  # 禁用自动调整表格宽度
    new_table.left_indent = Inches(0.5)  # 设置左缩进为0.3英寸
    set_table_col_widths(new_table, [1.8, 5])

    return new_table

def update_table(table, design_id, design_title, safety_description, ecu):
    """根据第一列的标题，将匹配到的内容填写到第二列中"""
    for row in table.rows:
        # 获取第一列的内容
        first_column = row.cells[0].text.strip()
        # 根据第一列的标题，填写第二列
        if first_column == "需求编号":
            if not row.cells[1].text.strip():  # 如果第二列是空的
                row.cells[1].text = design_id
                continue
        # elif first_column == "安全目标编号":
        #     if not row.cells[1].text.strip():
        #         row.cells[1].text = design_id
        #         break
        elif first_column == "需求名称":
            if not row.cells[1].text.strip():
                row.cells[1].text = design_title
                continue
        elif first_column == "需求描述":
            if not row.cells[1].text.strip():
                row.cells[1].text = safety_description
                continue
        elif first_column == "需求分配的ECU":
            if not row.cells[1].text.strip():
                row.cells[1].text = ecu
                continue


def write_requirements_to_word(target_path, result, requirement_types):
    # 打开Word文档
    template_file_word = 'export_templates/08 中国重汽 XX车型 整车安全需求规范 v2.0.docx'
    doc = Document(template_file_word)
    if doc == None:
        print(f'Error: {template_file_word} is not a valid Word document.')
        return

    # 遍历需求类型
    for req_type in requirement_types:
        #查找doc文档中的表格，如果表格第一行，第二列的内容等于req_type，则返回表格
        table = find_table_by_req_type(doc, req_type)
        if None == table:
            print(f'Info: {req_type} is not found in {template_file_word}.')
            continue
        # 1. 筛选出type等于req_type的数据
        filtered_type_data = []
        for item in result:
            if item['type'] == req_type:
                filtered_type_data.append(item)

        # 2. 使用defaultdict来存储每个code_dr的数据
        abbr_collections = defaultdict(set)

        # 遍历筛选后的数据，收集每个code_dr的abbr
        for item in filtered_type_data:
            abbr_collections[item['code_dr']].add(item['abbr'])

        # 将收集的数据写入表格
        for code_dr, abbrs in abbr_collections.items():
            # 找到第一个对应code_dr的数据，用于填充其他字段
            data = next(item for item in filtered_type_data if item['code_dr'] == code_dr)
            design_id = code_dr
            design_title = data['name_dr']
            safety_description = data['desc_dr']
            if abbrs:
                ecu = '、'.join(abbrs)
            else:
                ecu = 'N/A'
            # 更新表格内容
            update_table(table, design_id, design_title, safety_description, ecu)
            doc.add_paragraph()
            #如果是abbr_collections中的最后一个item,不需要增加表格
            if code_dr == list(abbr_collections.keys())[-1]:
                break
            #在本表格之后新创建一个表格
            table = add_new_table_after_table(doc, table)
    # 保存文档
    doc.save(target_path)
    print(f'Write requirements to {target_path} successfully.')


def export_all_reqs_to_word(target_path, pid):
    print(f'Start...export_all_reqs_to_word')
    #获取该项目所有的需求数据
    result = save_all_reqs_to_dict(pid)
    print(f'save_all_reqs_to_dict return {len(result)} records...')

    # 将数据写入到Word文档中
    write_requirements_to_word(target_path, result,requirement_types)




